locked
How to get the date and time in SQL statements RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have an aspx application which logs to a SQL db.  One part of this logging is to log the current date and time, and I use GetDate() to do this in various parts of the application. This logs detailed information e.g. 2007-04-30 13:10:02.047 (as at http://msdn.microsoft.com/en-us/library/ms188383.aspx).

    This work fine, but I need to either modify this function or use another on which just logs the yyyy-MM-DD HH:MM (e.g. 2007-04-30 13:10). I know that this can be done separately using a date and then time function, and/or by manipulating the information after it is got using some SQL statements.  However, I need to have one function like GetDate(), as this is used in Update statements in various parts of the code.

    I did search for answers on this, but can't see a function to do this so any suggestions appreciated.

    Stephen

    Thursday, May 10, 2012 10:53 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    You have to do the formatting when you select it not when you insert/update it. This is because since your datatype is datetime even if you try to save a value like 2007-04-30 13:10 it will get saved as 2007-04-30 13:10:00.000 resulting in the unwanted zeros when you select this value.

    So do not change your code where you use GetDate() to log the datetime. Do the formatting and ignore seconds and milliseconds when you select the value.

    • Marked as answer by SWClarke Monday, May 14, 2012 5:21 PM
    Thursday, May 10, 2012 11:45 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    You can use smalldatetime instead of datetime to store this information.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by SWClarke Monday, May 14, 2012 5:21 PM
    Friday, May 11, 2012 12:31 AM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    check-out below link

    http://www.sql-server-helper.com/tips/date-formats.aspx

    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Thursday, May 10, 2012 11:19 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    You have to do the formatting when you select it not when you insert/update it. This is because since your datatype is datetime even if you try to save a value like 2007-04-30 13:10 it will get saved as 2007-04-30 13:10:00.000 resulting in the unwanted zeros when you select this value.

    So do not change your code where you use GetDate() to log the datetime. Do the formatting and ignore seconds and milliseconds when you select the value.

    • Marked as answer by SWClarke Monday, May 14, 2012 5:21 PM
    Thursday, May 10, 2012 11:45 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    As stated, do it in the select , not the insert. Not knowing your version of sql this is backwards compatible prior to 2008:

    select convert(varchar(30),getdate(),111) 
	+ ' ' + CONVERT(varchar(3),DATEPART(hour, GETDATE()))
	+ ':' + CONVERT(varchar(3),DATEPART(minute, GETDATE())) as formattedTime

    www.minidba.com | Sql Server Performance Tuning & Monitoring Software

    Friday, May 11, 2012 12:22 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    You can use smalldatetime instead of datetime to store this information.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by SWClarke Monday, May 14, 2012 5:21 PM
    Friday, May 11, 2012 12:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for your replies and information.

    Stephen

    Monday, May 14, 2012 5:21 PM