getDate() Conversion

  • Hello Everyone,

    I am having bit of a problem I need to convert a date to a particular format before the job inserts the record. Hope someone can help

    I need the last field to be

    mm/dd/yyyy hh:mm:ss format. This is what I would do in VBScript for ASP when converting the date on the frontend for SQL Server

    MyDay = DatePart("d", now())

    Mymonth = DatePart("m", now())

    MyYear = DatePart("yyyy", now())

    MyHour = DatePart("h", now())

    MyMinute = DatePart("m", now())

    MySecond = DatePart("s", now())

    If LEN(MyDay) = 1 then

    MyDay = "0" & MyDay

    End IF

    If LEN(MyMonth) = 1 then

    MyMonth = "0" & MyMonth

    End IF

    If LEN(MyHour) = 1 then

    MyHour = "0" & MyHour

    End IF

    If LEN(MyMinute) = 1 then

    MyMinute = "0" & MyMinute

    End IF

    If LEN(MySecond) = 1 then

    MySecond = "0" & MySecond

    End IF

    MyDate = MyDay&"/"&MyMonth&"/"&MyYear&" "&MyHour&":"&MyMinute&":"&MySecond



    INSERT INTO HelpDsk (NAMEL, NAMEF, Priority, Office, Problem_Category, Sub_Category, SPA, Tech_Queue, Assigned_By, RING, EmailFLG, Date_Recieved)


    VALUES ("Willis", "Jason", "Normal", "PEN", "Task", "Network Status Report", "Jason.Willis", "Jason.Willis", "V.Holloman", "60A", "Y", getdate())

  • Here are my results with the below query -- 08/10/2004 09:13:34


    convert(varchar(20),getdate(),101)+' ' +convert(varchar(20),getdate(),108)

  • Dan that did the trick!!! I forgot that I need AM/PM too! But your help is greatly appreciated!!!

  • You are INSERTing GETDATE() into a table. Is the value Date_Received a DATETIME data type or a VARCHAR?

    If it's DATETIME, then format doesn't matter. SQL Server does not STORE dates and time in any 'format'.  DATETIME is stored as 8 bytes. 4 bytes are the number of days since the default date and 4 bytes are the number of 'ticks' (milliseconds) after midnight.

    Formatting DATETIME is only for DISPLAYing the date and time.


  • it is VARCHAR and the above works fine i just need the AM/PM part as well

  • This will put your am or pm in there for ya.  Enjoy


    select convert(varchar(20),getdate(),101)+' ' +convert(varchar(20),getdate(),108)+' ' +substring(convert(varchar(20),getdate(),100),18,2)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply