none
SQL Server DateTime Convert pattern

    Question

  • I need the datetime to be in the following format for SQL server 2005

    dd/MM/YYYY HH:MI in 24 Hour Format

    note: seconds are not included

     

    Thanks for any help in advance

    Tuesday, October 05, 2010 11:21 AM

Answers

  • I need the datetime to be in the following format for SQL server 2005

    dd/MM/YYYY HH:MI in 24 Hour Format

    note: seconds are not included

     

    Thanks for any help in advance


    Datetime datatypes are not stored using any format. SQL Server stores two integer values, four bytes each one. The first value is the number of days after or before "1900-01-01", and the second value is the number of 1/300 of a second after midnight.

    If you want to return a string representation of a datetime value, using specific format, then you can use function CONVERT. Same if you want to store specific format in the db, but you cannot use datetime datatype to store specific format.

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Ai-hua Qiu Thursday, October 14, 2010 9:25 AM
    Tuesday, October 05, 2010 12:26 PM
  • You may want to set seconds to 00 in the datetime value if I got your meaning correct. If so, try

    declare @d datetime
    
    set @d = getdate()
    
    select @d as WithSeconds, dateadd(second, datediff(second,'20100101',@d),'20100101') as WithoutSeconds
    
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Ai-hua Qiu Thursday, October 14, 2010 9:23 AM
    Tuesday, October 05, 2010 2:40 PM

All replies

  • select CONVERT(varchar,getdate(),103) + ' ' + CONVERT(varchar(5),getdate(),108)
    
    Tuesday, October 05, 2010 11:28 AM
  • try out 

    select convert(varchar(10), getdate(),103) +' '+  convert(char(5), getdate(),108) 

    -- hope that helps 

    Tuesday, October 05, 2010 11:29 AM
  • Thanks for quick reply but

    i need the data in datetime format

    for example 05/10/2010 17:15:23 as a datetime object not varchar

    i.e 05/10/2010 17:15 (it is not string it is datetime)

    Please Help me out in how to use in Convert(DATETIME,"data",DATEFORMAT) in sql

     

    Tuesday, October 05, 2010 11:46 AM
  • I edited my reply... check it.....

    Its format is 103... but not 101....

     

    you need to specify like convert(varchar,your datetime columnname here , 103)

    Tuesday, October 05, 2010 12:13 PM
  • The format for datetime type is: YYYY-MM-DD hh:mm:ss[.nnn]. The storage of seconds, or any other part can not be removed and when you take away an aspect, it ceases to be a datetime. e.g taking away .nnn, when you cast it back they will be defaulted to 000

    You should do the formatting in the application tier and let SQL Server deal with storage.

    There are some rare cases when you might need to have a custom type, but this is not one of them.

    Store it as a datetime, display it without mins/secs/msec and add a constraint if you need to make sure the data is as you need.


    Jon

    SET DATEFORMAT YMD
    DECLARE @dt DATETIME
    SET @dt = '20100101 22:00'
    SELECT CONVERT(CHAR(10),@dt,103)+SPACE(1)+CONVERT(CHAR(5),@dt,114) 
    
    
    SELECT CAST(CONVERT(CHAR(10),@dt,103)+SPACE(1)+CONVERT(CHAR(5),@dt,114) AS DATETIME)
    
    • Edited by Jon Gurgul Tuesday, October 05, 2010 12:16 PM missed example off ;p
    Tuesday, October 05, 2010 12:14 PM
  • Thanks for quick reply but

    i need the data in datetime format

    for example 05/10/2010 17:15:23 as a datetime object not varchar

    i.e 05/10/2010 17:15 (it is not string it is datetime)

     

     

    I actually didnt get ur question as jon gurgul said already datetiem format is CCYY-MM-DD hh:mm:ss.nnn nnn .. 

    is it that u want to remove the seconds part [default values to 0's ]  then use this date column in some join or so ???

    just simply then cast back to datetime or else u can manipulate & use date functions to get the absolute HH:MM values but the why do u need format ??? 

    Tuesday, October 05, 2010 12:25 PM
  • I need the datetime to be in the following format for SQL server 2005

    dd/MM/YYYY HH:MI in 24 Hour Format

    note: seconds are not included

     

    Thanks for any help in advance


    Datetime datatypes are not stored using any format. SQL Server stores two integer values, four bytes each one. The first value is the number of days after or before "1900-01-01", and the second value is the number of 1/300 of a second after midnight.

    If you want to return a string representation of a datetime value, using specific format, then you can use function CONVERT. Same if you want to store specific format in the db, but you cannot use datetime datatype to store specific format.

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Ai-hua Qiu Thursday, October 14, 2010 9:25 AM
    Tuesday, October 05, 2010 12:26 PM
  • You may want to set seconds to 00 in the datetime value if I got your meaning correct. If so, try

    declare @d datetime
    
    set @d = getdate()
    
    select @d as WithSeconds, dateadd(second, datediff(second,'20100101',@d),'20100101') as WithoutSeconds
    
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Ai-hua Qiu Thursday, October 14, 2010 9:23 AM
    Tuesday, October 05, 2010 2:40 PM