DateTime Conversion in SQL Server 2005

Question

I converted some string values to datetime as below:

'20131018'
'20130917'

by using

CONVERT(DATETIME, @date, 101)

I got the following

'2013-10-18 00:00:00.000'
'2013-09-17 00:00:00.000'

How would I remove the timepart of the values?

In SQL Server **2008**, you'll get `DATE` datatypes for just that - date-only. 2005 only has `DATETIME` and those values always have a time portion included. — marc_s, Oct 22 '13 at 21:06
Does the time component break anything, or is just visually annoying? — StingyJack, Oct 22 '13 at 21:16

score 0 · Answer 1 · answered Oct 22 '13 at 21:12

0

SUBSTRING ( expression ,start , length ) where expression will be your timestamp string and start will be 1 and length will be length of "yyyy-mm-dd" i.e. 10. for more reference please check http://technet.microsoft.com/en-us/library/ms187748.aspx

Check what said by "marc_s". Just use DATE instead of DATETIME.This will be better option.

answered Oct 22 '13 at 21:12

Gaurav Pant

4,029
6
31
54

True, `DATE` would be the better choice here - but that datatype is **NOT YET** available in SQL Server **2005** that the OP is using .... – marc_s Oct 22 '13 at 21:24
@ marc_s -- then we can use substring I think as I suggested. – Gaurav Pant Oct 22 '13 at 21:42

score 0 · Answer 2 · answered Oct 22 '13 at 21:45

Have some code I made. If you can figure out how to make this faster or better let me know! ;)

case --Format date into YYYYMMDD with placeholder 0's 
    when (CAST(DATEPART(DD,DATEADD(day, 7, <DATE>)) as int ) < 10) AND (CAST(DATEPART(MM,DATEADD(day, 7, <DATE>)) as int) < 10) 
    then CAST(DATEPART(YYYY,DATEADD(day, 7, <DATE>)) as CHAR(4)) 
        +'0'+ LTRIM(RTRIM(CAST(DATEPART(MM,DATEADD(day, 7, <DATE>)) as CHAR(2))))
        +'0'+LTRIM(RTRIM(CAST(DATEPART(DD,DATEADD(day, 7, <DATE>)) as CHAR(2))))
    when (CAST(DATEPART(MM,DATEADD(day, 7, <DATE>)) as int) < 10) 
    then CAST(DATEPART(YYYY,DATEADD(day, 7, <DATE>)) as CHAR(4)) 
        +'0'+LTRIM(RTRIM(CAST(DATEPART(MM,DATEADD(day, 7, <DATE>)) as CHAR(2))))
        +CAST(DATEPART(DD,DATEADD(day, 7, <DATE>)) as CHAR(2))
    when (CAST(DATEPART(DD, DATEADD(day, 7, <DATE>)) as int ) < 10) 
        then CAST(DATEPART(YYYY,DATEADD(day, 7, <DATE>)) as CHAR(4)) 
            + CAST(DATEPART(MM,DATEADD(day, 7, <DATE>)) as CHAR(2))
            +'0'+LTRIM(RTRIM(CAST(DATEPART(DD,DATEADD(day, 7, <DATE>)) as CHAR(2))))
    else CAST(DATEPART(YYYY,DATEADD(day, 7, <DATE>)) as CHAR(4)) 
        +CAST(DATEPART(MM,DATEADD(day, 7, <DATE>)) as CHAR(2))
        +CAST(DATEPART(DD,DATEADD(day, 7, <DATE>)) as CHAR(2))
end

Here's how it works. Put that after SELECT. Do a Find and Replace with and put your date in there. It checks the number of digits in the month and day, then inserts a 0 or two into the string when appropriate. I saved this block of code so that it would be easy to use when I encounter this problem. Hope it helps!

--EDIT-- It occurs to me, this might be the reverse of what you want to do...

DateTime Conversion in SQL Server 2005

2 Answers2