Date format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date format

Author

Topic

kalyformin
Starting Member

15 Posts

Posted - 2009-09-11 : 16:44:35

We have a field in one of our tables that displays date in the following format (MM//DD)

build_date
0101
0101
0901
1001
1201

Assuming the year is a constant (2009), is it possible to get the dates in this format?

build_date_corrected
01/01/2009
01/01/2009
09/01/2009
10/01/2009

Appreciate any help on this.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-11 : 16:51:52

I'm assuming that values are stored as VARCHAR (or some string data type), if they are integers let us know and we can make adjustments:

DECLARE @Table TABLE (build_date VARCHAR(4))

INSERT @Table
SELECT '0101'
UNION ALL SELECT '0101'
UNION ALL SELECT '0901'
UNION ALL SELECT '1001'
UNION ALL SELECT '1201'

SELECT CAST( '2009' + build_date AS DATETIME)
FROM @Table

kalyformin
Starting Member

15 Posts

Posted - 2009-09-11 : 17:43:46

Thank you!

I know I did not mention this in my previous post. Can we make the year dynamic, ie. based on the current year?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 12:59:39

quote:
Originally posted by kalyformin

Thank you!

I know I did not mention this in my previous post. Can we make the year dynamic, ie. based on the current year?

yup you can...just do like

SELECT CAST( CAST(YEAR(GETDATE()) AS char(4)) + build_date AS DATETIME)
FROM @Table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-14 : 02:18:07

or

SELECT CAST(DATENAME(YEAR,GETDATE()) + build_date AS DATETIME)
FROM @Table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources