Month and Year text field columns to Date field in SQL 2005

Question

Month and Year text field columns to Date field in SQL 2005

JaxieLaidey

SSC Enthusiast

Points: 161
More actions
May 17, 2013 at 7:08 am

#275366

In SQL 2005, I have 2 text field columns. One is month and the other is year. I would like to convert these 2 columns into a date field with this format: 8/1/2009
Thank you so much in advance 🙂
Jacky

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Richard Warr SSCertifiable Points: 6966 More actions · Answer 1

Hi Jacky,

To prevent any confusion does 8/1/2009 mean 8th January or August 1st? And what do the two current fields look like? ('1','01','Jan' etc.)?

JaxieLaidey SSC Enthusiast Points: 161 More actions · Answer 2

Richard Warr (5/17/2013)
Hi Jacky,
To prevent any confusion does 8/1/2009 mean 8th January or August 1st? And what do the two current fields look like? ('1','01','Jan' etc.)?

That would be August 1, 2009. Current fields are numbers in a text field, for example [month]='8' and [year]='2009'. There is no day field, so I would just like to plug 1 as the day. Thanks 🙂

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

If your Years and Months are valid (always a big if):

select

a.[Year],

a.[Month],

[Date]= dateadd(month,(12*a.[Year])-22801+a.[Month],0)

from

( --Test Data

select [Year] = '2008', [Month] = '2'union all

select [Year] = '2000', [Month] = '12'union all

select [Year] = '2011', [Month] = '1'

) a

Results:

Year Month Date

---- ----- -----------------------

2008 2 2008-02-01 00:00:00.000

2000 12 2000-12-01 00:00:00.000

2011 1 2011-01-01 00:00:00.000

JaxieLaidey SSC Enthusiast Points: 161 More actions · Answer 4

Thanks for your reply 🙂 However, how can I get the display to look like this '8/1/2009 00:00:00.000'? Right now it displays '2009-10-01 00:00:00.000'

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

JaxieLaidey (5/17/2013)
Thanks for your reply 🙂 However, how can I get the display to look like this '8/1/2009 00:00:00.000'? Right now it displays '2009-10-01 00:00:00.000'

SQL Server DATE or DATETIME columns do not have a format.

If you want to display a date in a particular format, you can use the CONVERT function. The style format of 101 is probably what you want.

CAST and CONVERT

http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx

Dwain Camps SSC Guru Points: 86978 More actions · Answer 6

I don't like using magic numbers in my date arithmetic, so I'd probably opt for something like this:

WITH TestData ([Year], [Month]) AS (

SELECT '2008', '2'UNION ALL

SELECT '2000', '12'UNION ALL

SELECT '2011', '1'

)

SELECT DATEADD(month, [Month]-1, [Year])

FROM TestData

Even though it might be just a bit slower.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St