select dd/mm/yyyy as yyymmdd - 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

SQL Server 2008 Forums

Transact-SQL (2008)

select dd/mm/yyyy as yyymmdd

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2012-10-14 : 06:13:28

I have a date like 13/09/2012 to yyyymmdd

How can i convert this so I can do calculations on the date using datediff

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 10:12:24

If the data is stored as a datetime type (date, datetime, smalldatetime etc.), you wouldn't need to do any conversion. If it is stored as character (char, varchar etc.), use convert as in:

CONVERT(DATETIME,YourCol,103);

For Example:

DECLARE @x VARCHAR(32) = '13/09/2012';
SELECT CONVERT(DATETIME,@x,103);
SELECT DATEDIFF(dd,CONVERT(DATETIME,@x,103),GETDATE());

Here, I am not specifically converting it to yyyymmdd format. There is no need to because it is best to provide the parameters to DATEDIFF function in its required format - which is datetime.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-10-16 : 09:45:04

Always use proper DATETIME datatype to store datetime values and leave format at front end application

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources