June 11, 2009 at 2:02 pm
I wanted to run this by you guys. I have a date field that I want to convert from an int to date time format (see below statement). I wrtinging a report in SSRS to generate a daily maintenance report of the jobs for the previous day. I thinking that there is a simpler way to write the convert.
SELECT CONVERT(datetime,CONVERT(varchar(8), run_date)) AS run_date3 from prime.msdb.dbo.sysjobhistory B
WHERE CONVERT(datetime,CONVERT(varchar(8), run_date)) >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 1, 0)
ORDER BY run_date3 DESC
Help will be appreciated. 🙂
June 11, 2009 at 4:52 pm
Try to use the following function.
You need to convert your int date to varchar and then try following function.
for example
declare @dateI int
declare @dateS varchar(10)
set @dateI =20060101
set @dateS = convert(varchar(10),@dateI)
select dbo.fn_FormatDate(@dateS,'yyyy/mm/dd')
create FUNCTION [dbo].[fn_FormatDate] (@date datetime, @format varchar(50))
RETURNS VARCHAR(50) AS
BEGIN
-------------------------------------------------------------------------------------
--
-- Name: FormatDate
-- Parameters:
--@date - Date, Date to be formatted
--@format - String, Template to format the date to
--
-- Returns: String, Date formatted to user requested template
--
-- Notes:
--1. Time information is not accounted for in this routine
--
--2. @format accepts the following values for each section of the date.
--Day
--dddd - Full Day Name
--ddd - Abbreviated Day Name
--dd - Zero Padded Day Number
--d - Day Number
--
--Month
--mmmm- Full Month Name
--mmm- Abbreviated Month Name
--mm- Zero Padded Month Number
--m- Month Number
--
--Year
--yyyy- Full 4 digit year
--yy- 2 digit year
--
--3. Any unexpected characters will be returned in the string
--
--4. Assumes database was set up with case-insensitive collation
--
-- Example Usage:
--All examples use the following date 2003-07-13 00:00:00
--
--'dddd, mmmm dd, yyyy' --> Sunday, July 13, 2003
--'mmddyyyy' --> 07132003
--'m-d-yy'--> 7-13-03
--'mm/dd/yyyy'--> 07/13/2003
--
-------------------------------------------------------------------------------------
DECLARE @pos AS INTEGER
DECLARE @char AS VARCHAR(1)
--
-- Replace Year
--
SET @pos = CHARINDEX('yyyy', @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 4, DATENAME(yyyy, @date))
--PRINT @format
SET @pos = CHARINDEX('yyyy', @format)
END
SET @pos = CHARINDEX('yy', @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 2, RIGHT(DATENAME(yyyy, @date) ,2))
--PRINT @format
SET @pos = CHARINDEX('yy', @format)
END
--
-- Replace Month
--
SET @pos = CHARINDEX('mmmm', @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 4, DATENAME(month, @date))
--PRINT @format
SET @pos = CHARINDEX('mmmm', @format)
END
SET @pos = CHARINDEX('mmm', @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(month, @date), 3))
--PRINT @format
SET @pos = CHARINDEX('mmm', @format)
END
SET @pos = CHARINDEX('mm', @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + CAST(DATEPART(month, @date) AS VARCHAR(2))), 2))
--PRINT @format
SET @pos = CHARINDEX('mm', @format)
END
SET @pos = CHARINDEX('m', @format)
WHILE @pos > 0
BEGIN
-- account for MArch and deceMBer
SET @char = SUBSTRING(@format, @pos + 1, 1)
IF (@char 'a') AND (@char 'b')
BEGIN
SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(month, @date) AS VARCHAR(2)))
--PRINT @format
SET @pos = CHARINDEX('m', @format)
END
ELSE
BEGIN
SET @pos = CHARINDEX('m', @format, @pos + 1)
END
END
--
-- Replace Day
--
SET @pos = CHARINDEX('dddd', @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 4, DATENAME(weekday, @date))
--PRINT @format
SET @pos = CHARINDEX('dddd', @format)
END
SET @pos = CHARINDEX('ddd', @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(weekday, @date), 3))
--PRINT @format
SET @pos = CHARINDEX('ddd', @format)
END
SET @pos = CHARINDEX('dd', @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + DATENAME(day, @date)), 2))
--PRINT @format
SET @pos = CHARINDEX('dd', @format)
END
SET @pos = CHARINDEX('d', @format)
WHILE @pos > 0
BEGIN
-- account for DEcember, sunDAy --> saturDAy, weDNesday
SET @char = SUBSTRING(@format, @pos + 1, 1)
IF (@char 'e') AND (@char 'a') AND (@char 'n')
BEGIN
SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(day, @date) AS VARCHAR(2)))
--PRINT @format
SET @pos = CHARINDEX('d', @format)
END
ELSE
BEGIN
SET @pos = CHARINDEX('d', @format, @pos + 1)
END
END
IF @format = '//' BEGIN
SET @format = ''
END
RETURN @format
END
June 11, 2009 at 4:59 pm
The date will always be in the format YYMMDD. I was looking simpler than what you posted.
June 11, 2009 at 5:46 pm
You can change to like this. YY
select dbo.fn_FormatDate(@dateS,'yy/mm/dd')
This function is so flexible to format any date. you can see the example in the function too.
June 11, 2009 at 6:44 pm
the CONVERT function has a third parameter which has some built in formats for converting datetime to varchar:
char to datetime:
select convert(datetime,'090611',12)
--results:
2009-06-11 00:00:00.000
and going the other way, datetime to char
select convert(varchar,getdate(),12)
--results:
090611
using the code 112 would use/give you a 4 digit year:
select convert(varchar,getdate(),112)
--results:
20090611
there are a LOT of formats available; take a look in BOLhere's ahandful of examples:
20090611-20:39:47:843 --- SELECT CONVERT(VARCHAR,@date,112) + '-' + CONVERT(VARCHAR,@date,114)
20090611-203947843 --- SELECT CONVERT(VARCHAR,@date,112) + '-' + REPLACE(CONVERT(VARCHAR,@date,114),':','')
06/11/2009 101 SELECT CONVERT(VARCHAR,@date,101)
2009.06.11 102 SELECT CONVERT(VARCHAR,@date,102)
11/06/2009 103 SELECT CONVERT(VARCHAR,@date,103)
11.06.2009 104 SELECT CONVERT(VARCHAR,@date,104)
11-06-2009 105 SELECT CONVERT(VARCHAR,@date,105)
11 Jun 2009 106 SELECT CONVERT(VARCHAR,@date,106)
Jun 11, 2009 107 SELECT CONVERT(VARCHAR,@date,107)
20:39:47 108 SELECT CONVERT(VARCHAR,@date,108)
Jun 11 2009 8:39:47:843PM 109 SELECT CONVERT(VARCHAR,@date,109)
06-11-2009 110 SELECT CONVERT(VARCHAR,@date,110)
2009/06/11 111 SELECT CONVERT(VARCHAR,@date,111)
20090611 112 SELECT CONVERT(VARCHAR,@date,112)
Lowell
June 15, 2009 at 10:32 am
I am easily able to format the date when it is in a character format; however, I want to convert from its original format interger field to a datetime time field. Is it possible? Below is the code and error message it get.
SELECT CONVERT(datetime, run_date,101) AS Run_Date, A.name, A.description as Description, message as Message, run_duration as Run_Duration, sql_severity as SQL_Severity
from prime.msdb.dbo.sysjobs A
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
June 15, 2009 at 10:56 am
drodriguez (6/15/2009)
I am easily able to format the date when it is in a character format; however, I want to convert from its original format interger field to a datetime time field. Is it possible? Below is the code and error message it get.
SELECT CONVERT(datetime, run_date,101) AS Run_Date, A.name, A.description as Description, message as Message, run_duration as Run_Duration, sql_severity as SQL_Severityfrom prime.msdb.dbo.sysjobs A
Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type datetime.
Try this:
SELECT cast(str(run_date) as datetime) FROM msdb.dbo.sysjobhistory
June 15, 2009 at 11:05 am
Yes, that worked and is slightly better. But is there anyway to convert from int to datetime?
Thanks,
Dave:-)
June 15, 2009 at 12:10 pm
Depends how the date is stored as an integer. In your case, since the date is stored in yyyymmdd format as an integer, no, you need to convert it to a string first.
June 15, 2009 at 12:16 pm
drodriguez (6/15/2009)
Yes, that worked and is slightly better. But is there anyway to convert from int to datetime?Thanks,
Dave:-)
Yes, you can convert directly from INTEGER to DATETIME. However, you cannot convert the integer in the format YYMMDD directly.
Try the following:
SELECT CAST(0 AS DATETIME);
You will get 1900-01-01 00:00:00.000 returned. Change the integer to 1 and you will get the next date. So, you would need to identify the actual integer value of your date - and then convert that integer value.
Lynn's solution is close, but won't work with a current integer date, for example today's integer date would be 90615.
The following works correctly, but may not be the best solution.
SELECT CAST(RIGHT('0' + STR({your integer date}, LEN({your integer date})) AS DATETIME);
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2009 at 12:17 pm
Thanks, Lynn. That's the answer I was looking for--just wanted to be sure.:-)
June 15, 2009 at 12:20 pm
I'll stick with converting it to a varchar first then a date field.
Thank for your guys help.
Dave
June 15, 2009 at 12:25 pm
drodriguez (6/15/2009)
I'll stick with converting it to a varchar first then a date field.Thank for your guys help.
Dave
Yeah, that is probably going to be the best option. If your dates are stored as YYYYMMDD - then it is simply a cast to char(8) and cast to datetime. If the dates are stored as YYMMDD (which is what I believe is being stored in those tables), then you have to account for the missing parts of the year for those years between 2000 and 2009.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2009 at 12:33 pm
Jeffrey Williams (6/15/2009)
drodriguez (6/15/2009)
I'll stick with converting it to a varchar first then a date field.Thank for your guys help.
Dave
Yeah, that is probably going to be the best option. If your dates are stored as YYYYMMDD - then it is simply a cast to char(8) and cast to datetime. If the dates are stored as YYMMDD (which is what I believe is being stored in those tables), then you have to account for the missing parts of the year for those years between 2000 and 2009.
Nope, stored as a four digit year. I just checked to verify. Seems dumb how MS did that, date and time in separate columns. Why didn't they just store it in one datetime column as a datetime value??
June 15, 2009 at 12:40 pm
Lynn Pettis (6/15/2009)
Nope, stored as a four digit year. I just checked to verify. Seems dumb how MS did that, date and time in separate columns. Why didn't they just store it in one datetime column as a datetime value??
Ahh, yes - I should have double checked that myself. I was thinking about the time - which is stored the same way but you have to consider the leading 0's.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply