locked
How to convert nvarchar to datetime data-type? RRS feed

  • Question

  • How to convert nvarchar to datetime data-type?

    Thanks


    regards,
    Kumar
    Monday, November 23, 2009 3:45 PM

Answers

All replies

  • IF ISNULL(ISDATE((@StringVar ),0)=1
    BEGIN
           SELECT CAST(@StringVar as datetime)
    END
    Monday, November 23, 2009 3:48 PM
  • It depends on how your data is saved in the nvarchar field. Can you psot an example and what is the final format you want it in. Here is an example

    DECLARE @t NVARCHAR(20)
    SELECT @t = '01-01-2009'
    SELECT CONVERT(DATETIME,@t)
    

    Abdallah, PMP, ITIL, MCTS
    Monday, November 23, 2009 3:48 PM
  • You have to use the appropriate style number (103 in the demo) with CONVERT:

    -- SQL Server 2008 T-SQL code
    DECLARE @ndate nvarchar(32) = N'15/03/2010'
    SELECT CONVERT(datetime, @ndate, 103)
    -- 2010-03-15 00:00:00.000
    
    

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Friday, September 28, 2012 2:41 PM
    Monday, November 23, 2009 3:50 PM
  • Hi All,

    I have data something is as below:

    10102007
    07102008 -- basically mmddyyyy format

    when I'm trying to write:-

    declare @strdate nvarchar(50)
    set @strdate = N'10102007'
    select convert(datetime, @strdate, 106)


    but I'm getting error as:-

    "Arithmetic overflow error converting expression to datat type datetime"


    any idea?

    Thanks



    regards,
    Kumar
    Monday, November 23, 2009 4:47 PM
  • Kumar,

    You have to use the 101 style number with STUFF formatting. Demo follows.

    -- SQL Server 2008 T-SQL code DECLARE @ndate nvarchar(32) = N'03152010' SELECT CONVERT(datetime, stuff(stuff(@ndate,3,0,'/'),6,0,'/'), 101) -- 2010-03-15 00:00:00.000

    Datetime conversion & date and time functions:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Proposed as answer by Melissa Suciadi Thursday, November 26, 2009 2:53 AM
    • Marked as answer by Zongqing Li Friday, November 27, 2009 8:25 AM
    • Edited by Kalman Toth Friday, September 28, 2012 2:41 PM
    Monday, November 23, 2009 5:13 PM
  • Hi SQLUSA,
      I tried with stuff formatting as you mentioed above but still I am getting the below error

    Msg 8115, Level 16, State 2, Line 5

    Arithmetic overflow error converting expression to data type datetime.

    Please help me out

    Wednesday, February 3, 2010 10:58 AM
  • The best string date format is ISO format YYYYMMDD (no dashes).
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, February 7, 2010 1:36 AM
  • Thank you Kumar. Your query help me a lot.

    Best regards.

    Monday, September 8, 2014 10:30 PM