August 10, 2004 at 6:41 am
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())
August 10, 2004 at 7:20 am
Here are my results with the below query -- 08/10/2004 09:13:34
select
convert(varchar(20),getdate(),101)+' ' +convert(varchar(20),getdate(),108)
August 10, 2004 at 7:33 am
Dan that did the trick!!! I forgot that I need AM/PM too! But your help is greatly appreciated!!!
August 10, 2004 at 7:53 am
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.
-SQLBill
August 10, 2004 at 8:00 am
it is VARCHAR and the above works fine i just need the AM/PM part as well
August 10, 2004 at 9:42 am
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