none
varchar to date

    Question

  • I have the string that is stored in the format yyyymmdd (ex : 20121029). I need to convert it to date.

    I tried convert(datetime,field_name,101) and i get something like 2012-10-29 00:00:00.000 , I dont want time part, How do i do that?

    Thanks

    Wednesday, October 09, 2013 7:06 PM

Answers

  • If I do

    convert(date,'20131009',101) , I get

    Msg 243, Level 16, State 1, Line 1

    Type DATE is not a defined system type.

    Which version of SQL Server are you running?

    select @@version

    If the result is less than Microsoft SQL Server 2008 then the date data type isn't available.

    In that case you will have to live with the time part or use a varchar data type instead of the datetime data type.  Either way you should be able to truncate the time part when you need the date only.

    Wednesday, October 09, 2013 7:22 PM
  • Here's an example that does basically what you are looking for I believe (I tested it on SQL Server 2012 so no guarantees):

    declare @MyDate varchar(20) = '1/1/2013'
    select convert(datetime, @MyDate, 101), convert(varchar(20), convert(datetime, @MyDate, 101), 101)

    • Marked as answer by billu badshah Wednesday, October 09, 2013 7:48 PM
    Wednesday, October 09, 2013 7:27 PM

All replies

  • select convert(date,'20131009',101)
    Wednesday, October 09, 2013 7:14 PM
  • The datetime date type in SQL Server contains a "time part" and there's no way to get around that.

    If you are using SQL Server 2008 or later there's a date data type which doesn't contain the time.

    In other words...

    cast(field_name as date)

    or

    convert(date, field_name, 101)

    Wednesday, October 09, 2013 7:14 PM
  • If I do

    convert(date,'20131009',101) , I get

    Msg 243, Level 16, State 1, Line 1

    Type DATE is not a defined system type.

    Wednesday, October 09, 2013 7:17 PM
  • If I do

    convert(date,'20131009',101) , I get

    Msg 243, Level 16, State 1, Line 1

    Type DATE is not a defined system type.

    Which version of SQL Server are you running?

    select @@version

    If the result is less than Microsoft SQL Server 2008 then the date data type isn't available.

    In that case you will have to live with the time part or use a varchar data type instead of the datetime data type.  Either way you should be able to truncate the time part when you need the date only.

    Wednesday, October 09, 2013 7:22 PM
  • Thanks Ed for your answer.

    I am running :

    Microsoft SQL Server 2005 - 9.00.5000.00 (X64)   Dec 10 2010 10:38:40   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

    Looks like I will have to truncate time part to get around it.

    Wednesday, October 09, 2013 7:25 PM
  • Here's an example that does basically what you are looking for I believe (I tested it on SQL Server 2012 so no guarantees):

    declare @MyDate varchar(20) = '1/1/2013'
    select convert(datetime, @MyDate, 101), convert(varchar(20), convert(datetime, @MyDate, 101), 101)

    • Marked as answer by billu badshah Wednesday, October 09, 2013 7:48 PM
    Wednesday, October 09, 2013 7:27 PM
  • strip off the time part, if you dont need it

    SELECT CONVERT (CHAR(11), convert(datetime,GETDATE(),110) )

    Wednesday, October 09, 2013 7:30 PM