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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy