none
convert varchar to datetime

    Question

  • Hi

    I need to convert this so the output is datetime like this 'dd/mm/yyyy'.
    Anybody any ideas on how to do that?

    SELECT 

    CONVERT(varchar, @dag) + '/' + CONVERT(varchar, @maand) + '/' + CONVERT(varchar, @jaar) AS Datum

    Thnx Marijn
    Wednesday, April 22, 2009 7:02 AM

All replies

  • Hello,

    Try this.

    SELECT

     

    CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]

    Thanks,
    Shanthi

    Wednesday, April 22, 2009 7:05 AM
  • Hi
    I hope Below query will fulfil your requirement

    SELECT CONVERT(VARCHAR(25),GETDATE(),103)

    Thanks

     

    Ramesh.M

    Wednesday, April 22, 2009 7:07 AM
  • Thnx for the answer but i need the dd/mm/yyyy in datetime format not varchar :)
    Wednesday, April 22, 2009 7:09 AM
  • HI,
    Is this works?
    SELECT DATEPART(DAY,GETDATE()) AS [Day],DATEPART(MM,GETDATE()) as 'Month',DATEPART(YY,GETDATE())as 'Year'

    Wednesday, April 22, 2009 7:15 AM
  • Depending on the regional setting you can use the '/' or not.
    To avoid this I would put the string to convert into the YYYYMMDD format, which is non-ambiguous.

    So it would look like this:
    select	CAST(CONVERT(varchar, @jaar) +
    
    		REPLICATE ( '0', 2 - LEN(@Maand)) + CONVERT(varchar, @maand) +
    
    		REPLICATE ( '0', 2 - LEN(@dag)) + CONVERT(varchar, @Dag) AS DateTime )
    
    		AS DATUM
    
    
    Wednesday, April 22, 2009 7:27 AM
  • thnx again

    But same comment. It's not datetime but integer now :)
    Wednesday, April 22, 2009 7:28 AM
  • Hello,

    You cannot have day first when converting it to datatime. You can have MM/DD/YYYY but not DD/MM/YYYY.

    You can have both the formats in Varchar but not in datetime.

    try this below select statements

    First select statement will executes without an error as the day is 12 which will be considered as month, coming to that of the second select ststement it will raise an error out-of-range error.

    SELECT CAST(RIGHT('00'+ '12',2) +'/'+ RIGHT('00'+ CAST(DATEPART(mm,GETDATE()) AS NVARCHAR),2)+'/'+ CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR) AS DATETIME) , GETDATE()
    
    
    SELECT CAST(RIGHT('00'+ '13',2) +'/'+ RIGHT('00'+ CAST(DATEPART(mm,GETDATE()) AS NVARCHAR),2)+'/'+ CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR) AS DATETIME) , GETDATE()

    For this you need to change lenguage settings

    Hope its clear...


    Pavan http://www.ggktech.com
    Wednesday, April 22, 2009 7:32 AM
  • Hi,

    You can try the below query

     

    set language 'British English'
    SELECT 
    convert(DATETIME, CONVERT(varchar, 22) + '/' + CONVERT(varchar, 04) + '/' + CONVERT(varchar, 2009))	AS Datum
    

    Chandra, http://www.ggktech.com
    Wednesday, April 22, 2009 7:38 AM
  • One thing you can try is setting th dateformat for the session.

    This will give you error

    select convert(datetime,'15/01/2009')
    
    

    So set dmy format

    SET DATEFORMAT dmy
    
    select convert(datetime,'15/01/2009')
    
    
    

     


    Mangal Pardeshi
    SQL With Mangal
    Wednesday, April 22, 2009 7:46 AM
    Moderator
  • thnx again

    But same comment. It's not datetime but integer now :)

    I'm not quite following here. The result has been casted to a datetime...
    Wednesday, April 22, 2009 7:51 AM
  • set dateformat dmy
    SELECT 
    convert(int, convert(DATETIME, CONVERT(varchar, 22) + '/' + CONVERT(varchar, 04) + '/' + CONVERT(varchar, 2009)))	AS Datum
    

    Chandra, http://www.ggktech.com
    Wednesday, April 22, 2009 7:55 AM
  • I was replying to the second answer.
    Wednesday, April 22, 2009 9:14 AM