CODEWITHSUNDEEP
sqlsql-server
birdy
birdy

Reputation: 9636

convert string to date in sql server

How do I convert YYYY-MM-DD (2012-08-17) to a date in SQL Server?

I don't see this format listed on the help page: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Upvotes: 22

Views: 242189

Answers (5)

Beyhan
Beyhan

Reputation: 99

Write a function

CREATE FUNCTION dbo.SAP_TO_DATETIME(@input VARCHAR(14))
RETURNS datetime
AS BEGIN
    DECLARE @ret datetime
    DECLARE @dtStr varchar(19)

    SET @dtStr = substring(@input,1,4) + '-' +  substring(@input,5,2) + '-' + substring(@input,7,2)
                + ' ' + substring(@input,9,2) + ':' + substring(@input,11,2) + ':' + substring(@input,13,2);        

    SET @ret = COALESCE(convert(DATETIME, @dtStr, 20),null);
    RETURN @ret
END

Upvotes: 0

Randy Worrell
Randy Worrell

Reputation: 33

I had a similar situation. Here's what I was able to do to get a date range in a "where" clause (a modification of marc_s's answer):

where cast(replace(foo.TestDate, '-', '') as datetime) 
      between cast('20110901' as datetime) and
              cast('20510531' as datetime)

Hope that helps...

Upvotes: 1

dream
dream

Reputation: 95

if you datatype is datetime of the table.col , then database store data contain two partial : 1 (date) 2 (time)

Just in display data use convert or cast.

Example:

create table #test(part varchar(10),lastTime datetime)
go

insert into #test (part ,lastTime )
values('A','2012-11-05 ')

insert into #test (part ,lastTime )
values('B','2012-11-05 10:30')


go

select * from #test 

A   2012-11-05 00:00:00.000
B   2012-11-05 10:30:00.000

select part,CONVERT (varchar,lastTime,111) from #test

A   2012/11/05
B   2012/11/05

select part,CONVERT (varchar(10),lastTime,20) from #test 

A   2012-11-05
B   2012-11-05

Upvotes: 0

CodeLikeBeaker
CodeLikeBeaker

Reputation: 21312

This will do the trick:

SELECT CONVERT(char(10), GetDate(),126)

Upvotes: 6

marc_s
marc_s

Reputation: 754258

I think style no. 111 (Japan) should work:

SELECT CONVERT(DATETIME, '2012-08-17', 111)

And if that doesn't work for some reason - you could always just strip out the dashes and then you have the totally reliable ISO-8601 format (YYYYMMDD) which works for any language and date format setting in SQL Server:

SELECT CAST(REPLACE('2012-08-17', '-', '') AS DATETIME)

Upvotes: 28

Related Questions