Convert DateTime To Month/Year

Hi.. can you please supply a SQL statement which will convert a datetime value to a Month Year format?

2001-07-17 14:32:20.763 ---> July 2001

thankyou !

View Replies


How To Convert Calendar Month Year To Fiscal Month Year

I have a calendar date field in my table. I want to add one more field (Fiscal Month/Year)in my table on basis of Calendar Date filed.
Our fiscal year starts from October i.e. Calendar Month/ Year : 10/2007 =Fiscal Month/Year: 01/2008

The end result which i am looking for :

CalendarMonth Year
Fiscal Month Year
















View Replies View Related

Convert Month Name, Year Range To Mm/dd/yy

Using SQL 2000. I need to select rows based on a date range. Both thebeginning date and ending date of the range will be entered in themm/dd/yy format. There are 2 columns in the table called MonthOfEvaland YearOfEval, both varchar(9) and both with data like "August" and"2005-2006". I think I need to use these 2 columns to create a date inthe mm/dd/yy format, then use that newly created date to see if it'swithin the date range. How would I go about doing this or is there abetter way?Thanks for any help or advice.

View Replies View Related

Update Day,month And Year Of A Datetime

using MS SQL Server, I want to update only the day,month and year portion of a Datetime field.

So, i want to create an update query to set the day, month and year the same in each record but leave the datepart unchanged.

This gives me an error message:
SET DATEPART(dd, MeetingTimeNEW) = 1, DATEPART(mm, MeetingTimeNEW) = 1, DATEPART(yy, MeetingTimeNEW) = 2006

In my mind, an electrician, it make sense to only update each part of the whole. However.........

View Replies View Related

Compare Only Month And Year Part In Datetime Type

hai friends,
iam doing a project in .net and using sql server.
i need to compare only month and year part in datetime type to retrive data.
1)retrive unique year and its months available in the database.
like may 2008
apr 2008
mar 2007

View Replies View Related

Updating Datetime Data Types In A Table To Show Just Day, Month, Year

I have a table with a datetime field 'TheDate'. Currently dates are stored as 'mm-dd-yyyy 00:00:00'. Is there a way to get just the month, day and year parts, '01/01/2008' into the field without changing the field data type to varchar? I'm asking because when I do this:

declare @MyDate as datetime

set @MyDate = '04/02/2008 18:00:00'

select substring(convert(varchar,@MyDate,101),1,10)

I get '04/02/2008', but when I do this:

update TheTable
set TheDate = substring(convert(varchar,TheDate,101),1,10)

I'm still getting a date in the format 'mm-dd-yyyy 00:00:00' stored in the table. I'd like to be able to lose the time portion, but I'd like to be able to keep the datetime datatype for date math purposes. Can it be done?

View Replies View Related

Very Simple Question Regarding A DATETIME Field; Select Fields Matching Month/day/year

Hello All,I've got a DATETIME field, and it includes hour:minutes:second data.  I want to do selects where I can simply match on the month, day and year.  For instance, something like this:SELECT * FROM QuizAttempts WHERE DateTimeTaken = '1/12/2006'And have it match anything that was taken that day, regardless of *when* it was taken.  Any suggestions?Thanks!  -Josh

View Replies View Related

Grouping My Month/year Desending By Month/year

i have some classes that I want to group by month/year (note:i dont need the day of the month)
how do i wirte my sql so it only gives me the dictinct groups month/year  of the classes I have so that it comes out like so..
i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.
dbo.classgiven.classdate date of the class.thank you all 
SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/"  + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode   FROM  dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE ( = '-1') AND  (dbo.classT.coned IS NOT NULL)", conNorthwind )

View Replies View Related

CONVERT DATETIME To VARCHAR With Code 106 Does Not Show Long Month

I am having a problem while converting datetime to varchar with code 106. Here is the code and result I get:

TSQL Code:

"26 10 2007"

My expected result:
"26 Oct 2007" or "26 October 2007"

Have you encountered this problem before? Is it related to the SQL server setting? Please help and thank you in advance.

View Replies View Related

ERROR: &&"Year, Month, And Day Parameters Describe An Un-representable DateTime&&" In Oracle Provider For OLE DB

In my SSIS package I am trying to connect to a database in Oracle using an Oracle Provider for OLE DB.
I get the following error:
at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
at System.Data.ProviderBase.DbBuffer.ReadDateTime(Int32 offset)
at System.Data.OleDb.ColumnBinding.Value_DBTIMESTAMP()
at System.Data.OleDb.ColumnBinding.Value()
at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbMetaDataFactory.PrepareCollection(String collectionName, String[] restrictions, DbConnection connection)
at System.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection connection, String collectionName, String[] restrictions)
at System.Data.ProviderBase.DbConnectionInternal.GetSchema(DbConnectionFactory factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection, String collectionName, String[] restrictions)
at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName, String[] restrictionValues)
at Microsoft.DataWarehouse.Design.OleDbSchema.GetSchema(String collectionName, String[] restrictionValues)
at Microsoft.DataWarehouse.Design.DataSourceConnection.GetSchemaTable(DataTable& dataTable, String collectionName, String[] restrictions)
at Microsoft.DataWarehouse.Design.DataSourceConnection.ConvertGetSchema(DataTable& dataTable, Guid schemaEnum, Object[] restrictions)
at Microsoft.DataWarehouse.Design.DataSourceConnection.GetSchemaTable(DataTable& dataTable, Guid schemaEnum, Object[] restrictions)
at Microsoft.DataTransformationServices.Design.ComboBoxWithTables.GetTableInfos(DataSourceConnection dataSourceConnection, String connectionName)
at Microsoft.DataTransformationServices.Design.ComboBoxWithTables.FillTableOrViewComboBox()

Why this is not working for 10.2?
The same package works fine for Oracle 8.1.7

View Replies View Related

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,

View Replies View Related

Select Month/year When Range Spans Year

I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.

The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.

I've tried evaluating the parts of the episode date seperately like:

(epimonth>=10 and epiyear=2003)
(epimonth<=2 and epiyear=2004)

and that works, but what happens when someone wants to see from 10/2002 to 2/2004?

Any suggestions on the best way to do this?

View Replies View Related

Getting Date In Day/month/year Instead Of Month/day/year

I am trying to get my db to return a date in the format day/month/year but its returning the american version month/day/year.

I'm using a DatePart function that converts my date:

( @fDate datetime )
RETURNS varchar(10)
RETURN ( CONVERT(varchar(10),@fDate,101) )

This returns te american version, can anyone help me to get this to convert the UK way.


View Replies View Related

Get Last Day Of Month For Agiven Month And Year

Does anyone know how I can get last day of month
if I pass a function a given month and and given year.
@Month = 2
@Year = 2004
The result I would need is 29 because there are 29 in
the month of February in the 2004.
Any help on this is greatly appreciated.

View Replies View Related

Get Last Day Of Month For Agiven Month And Year

Does anyone know how I can get last day of month
if I pass a function a given month and and given year.
@Month = 2
@Year = 2004
The result I would need is 29 because there are 29 in
the month of February in the 2004.
Any help on this is greatly appreciated.

View Replies View Related

How Can I Retrieving Records By Past One Week,past Month, Past Quarter Of Year And Then Half Year.

Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.

View Replies View Related

Get The First And Last Month Of The Year

hi! i have this following query:Select DATEADD(YEAR, -2, @sDate),(dateadd(dd,-(day(dateadd(yy,1,@sDate))),dateadd(yy,1,@sDate)))..the results of these are:2005-01-01 ,2007-12-31 but i want 2007-12-31 to be can i do that using dateadd function?


View Replies View Related


i have to input month/year can u tell me what would the datatype of that column? - should it be datetime or just month/year? what would be the datatype?

i have to make function called getQuantityByMonth that when given the following inputs:
input: campaign,begin month/year, end month/year

returns the item sales for the items in a campaign with the following column headers...

output: period, periodint, campaign, itemnmbr, totalquantity

@startmonthyear datetime
@endmonthyear datetime

This is hardcoded, but the output of this is an example of what i would want. The purpose of this is to be able to do a multi-line graph.

select 'Oct 07' as Period, 200710 as PeriodInt, campaign,itemnmbr, totalquantity from



select 'Nov 07' as Period, 200711 as PeriodInt, campaign,itemnmbr, totalquantity from



select 'Dec 07' as Period, 200712 as PeriodInt, campaign,itemnmbr, totalquantity from



select 'Jan 08' as Period, 200801 as PeriodInt, campaign,itemnmbr, totalquantity from



select 'Feb 08' as Period, 200802 as PeriodInt, campaign,itemnmbr, totalquantity from



select 'Mar 08' as Period, 200903 as PeriodInt, campaign,itemnmbr, totalquantity from


order by itemnmbr, periodint

thanks for the help.

View Replies View Related

Month && Year Comparison

Hi,The problem here in hand is comparing the month & year ranges rather than the entire date. I am passing a start month and start year along with the end month and end year, now the query that i have written does not seem to be appropriateMy Query:MONTH(Date_Added) BETWEEN @StartMonth AND @EndMonthAND YEAR(Date_Added) BETWEEN @StartYear AND @EndYearFor some reason this works fine if i specify the startmonth as 1 and endmonth as 12, whereas if i specify 1 and 1 it doesnot work. Can anyone help me by refining my query so that i can search a particular date within mm/yyyy and mm/yyyy. Thanks in advance.Santosh   

View Replies View Related

Using Month Year Table

Hi,We are using Month-year tables to keep the history of long transactionof our application. For example:We capture the details of a certain action in table"TransDtls<CurrMonth><CurrYear>" (this month: TransDtls072005).This way tables keep growing. every month a new table gets created. Wehave done it because we estimated that every month year table willcarry around 2 - 3 Lac records and most of the time the operations willwork on current month year table.Avoiding this way and carrying on with single table instead of "Monthyear" table might lead us system performance issues.But now we are a bit confused on the way we are heading and also facingthe implementation issues like manipulating data from different"month-year" tables. Could anyone please help us to make our visionclear on this?Looking for your valuable comments.Thanks.

View Replies View Related

Group By Month,day,year..

I tried below but it doesnt work

select date_field from tbl group by DAY(date_field)

View Replies View Related

Year, Month Columns

My data is mainly updated each month. Most of my tables have a month and year column. I have many statements such as this when I need to join tables:

SELECT equity FROM vMonthlyFirmSummary WHERE MonthlyInputs.year = vMonthlyFirmSummary.year AND MonthlyInputs.month = vMonthlyFirmSummary.month

Is this bad database technique? Do you think instead I should try to create a string key field such as 2007-01 (Year - Month)? Please give any insight on how to make the DB simpler. Thanks.

View Replies View Related

Month Year Where Clause

Hi i have a where clause which i think i need to change

i currently have the code below but dont think it right
i think it needs to be (O_Date) between @month1 @year AND @month2 @year2, but not sure how this should be done?

WHERE MONTH(O_Date) Between @month1 AND @month2
and YEAR(OW.O_Date) Between @year AND @year2

View Replies View Related

Order By Month And Year

hai freinds ,

This is the query iam using to retrieve year and its month from a datetime column

quote:Select Distinct DateName(mm, col_datetime) as 'Month',
as 'Year'from table_name
Order By DateName(mm, col_datetime)

result for the above query is

month year
june 2008
june 2007
july 2007
july 2008
aug 2007
aug 2008 and so on.....

but my need is

month year
june 2008
july 2008
aug 2008
june 2007
july 2007
aug 2007
and so on.....

so can someone fix it or give me a idea

thanks friends

View Replies View Related

See If Date Is Same Year And Month And Day


I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.

SO I need to do:

IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)

But it has to be comparing the year,month and day (excluding the time part of the datetime type).

View Replies View Related

Current Month && Year Query?

I am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1.  I keep getting this error Line 5: Incorrect syntax near '='.
If I comment out the case statement just evaluate the  (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?1    SELECT     CONVERT(char(10), User_Date, 101) AS userdate, COUNT(*) AS CNT, User_Name2    FROM         Users3    WHERE     (User_Name = 'Joe Bob') AND (MONTH(User_Date) = MONTH(DATEADD(mm, - 1, GETDATE()))) AND 4    case  MONTH(User_Date) when  1 then 5      (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))6    else (YEAR(User_Date) = YEAR(GETDATE()))7    END8    GROUP BY CONVERT(char(10), User_Date, 101), User_Name9    ORDER BY CONVERT(char(10), User_Date, 101), User_Name 

View Replies View Related

Display ONLY Month, Year From SQL2k

Tried this: SELECT CONVERT(varchar,fieldMonthYear,107) 'Month in Question' FROM .....That returns: Apr 01, 2006What I need is this: April 2006 or even Apr 2006. But no date for the day.Is there a way I can trim the center 4 characters of this now converted varchar? This is in a datalist, btw. Thanks!bs. 

View Replies View Related

Select On The Basis On Month And Year

hi guys.

I have a datetime column in my SQL server database.. I need to select the value from the table by passing month and year only..

any suggestions..??

Thanks in advance..


View Replies View Related

Last Year Month To Date Function

I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex ***

View Replies View Related

Want To Concatenate Year And Month To Get A Date


My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

I'll appreciate your help.

YearMonthDerived Date
and so on ....

Many thanks in advance. I'll appreciate your help
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

View Replies View Related

Copyrights 2005-15, All rights reserved