November 27, 2011 at 11:14 am
Dear All,
i have column in my table which of datatype varchar(14) (20111107070115) , now i want to change display
2011/11/07 07:01:15 ( DD/MM/YYYY HH:MM:SS)
please help me on this , i am new to this, i would have like to report on based on this.
Highly appreciated
November 27, 2011 at 11:23 am
rashid_alavi (11/27/2011)
Dear All,i have column in my table which of datatype varchar(14) (20111107070115) , now i want to change display
2011/11/07 07:01:15 ( DD/MM/YYYY HH:MM:SS)
please help me on this , i am new to this, i would have like to report on based on this.
Highly appreciated
You want 24 hour time or 12 hour time?
As a side bar, this is why one should never store dates and times as VARCHAR... is just makes for a PITA. 😉 Of course, sometimes it's not something that we did and just have to deal with it but it's still a good lesson to learn.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2011 at 1:02 pm
rashid_alavi (11/27/2011)
Dear All,i have column in my table which of datatype varchar(14) (20111107070115) , now i want to change display
2011/11/07 07:01:15 ( DD/MM/YYYY HH:MM:SS)
please help me on this , i am new to this, i would have like to report on based on this.
Highly appreciated
If you must contain date/time values in a varchar datatype (which is a bad idea), then I'd suggest leaving it in the format YYYYMMDDHHMMSS. This at least allows the column to be properly indexed and ordered. For example:
select *
from mytable
where mydate >= '20111101' and mydate < '20111201'
order by mydate;
Any conversion to DD/MM/YYYY should be done on the client/reporting side or perhaps in a view.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 28, 2011 at 2:30 pm
I agree with the other posts: if possible, change this to a real date type field. Then changing the display in a procedure or application is a simple matter.
If you can't change how the data is stored in this column, then how about adding a computed column to this table that does store the data as a date type. Then you can base your reports (or whatever) on the computed column.
If a computed column won't work, then a variation on that theme would be to add a regular date type column and use an AFTER trigger to populate the date column.
November 29, 2011 at 4:53 am
hi All,
I Dont want to change on the DB , i want to have that display as YYYY/MM/DD when i want to generate reports for the application reports, the database is MSSQL Server 2005 , i am not getting the right syntax to put it accross.
appreciate if anyone can give idea on this.
November 29, 2011 at 4:55 am
rashid_alavi (11/29/2011)
hi All,I Dont want to change on the DB , i want to have that display as YYYY/MM/DD when i want to generate reports for the application reports, the database is MSSQL Server 2005 , i am not getting the right syntax to put it accross.
appreciate if anyone can give idea on this.
Then let your application / reports handle it.
November 29, 2011 at 5:21 am
The issue is what do you do when you have an invalid date? With the data being stored as VARCHAR, you almost certainly will have some invalid dates in there 😉
Here's some sample data -
BEGIN TRAN
SET NOCOUNT ON
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
varcharDateTime
INTO #testEnvironment
FROM (SELECT TOP 999999
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),(RAND(CHECKSUM(NEWID())) * 30000) + CAST('1945' AS DATETIME),20),'-',''),':',''),' ','') AS varcharDateTime
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
UNION ALL
SELECT '20190231191114' --INVALID DATE!!
) a
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT COUNT(*) FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
ROLLBACK
Here's a couple of SQL options
PRINT '========== CONVERT ==========' --WILL NOT PICK UP ON THE INVALID DATE I HAVE ADDED
SET STATISTICS TIME ON
SELECT SUBSTRING(varcharDateTime,1,4) + '/' + SUBSTRING(varcharDateTime,5,2) + '/' + SUBSTRING(varcharDateTime,7,2) + ' ' +
SUBSTRING(varcharDateTime,9,2) + ':' + SUBSTRING(varcharDateTime,11,2) + ':' + SUBSTRING(varcharDateTime,13,2),
varcharDateTime
FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CONVERT WITH CASE ==========' --WILL PICK UP ON THE INVALID DATE I HAVE ADDED
SET STATISTICS TIME ON
SELECT formattedVarcharDateTime, varcharDateTime
FROM (SELECT SUBSTRING(varcharDateTime,1,4) + '/' + SUBSTRING(varcharDateTime,5,2) + '/' + SUBSTRING(varcharDateTime,7,2) + ' ' +
SUBSTRING(varcharDateTime,9,2) + ':' + SUBSTRING(varcharDateTime,11,2) + ':' + SUBSTRING(varcharDateTime,13,2) AS formattedVarcharDateTime,
CASE WHEN varcharDateTime NOT LIKE '[1-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-2][0-9][0-5][0-9][0-5][0-9]'
THEN 0
WHEN ISDATE(SUBSTRING(varcharDateTime,1,4) + '/' + SUBSTRING(varcharDateTime,5,2) + '/' + SUBSTRING(varcharDateTime,7,2) + ' ' +
SUBSTRING(varcharDateTime,9,2) + ':' + SUBSTRING(varcharDateTime,11,2) + ':' + SUBSTRING(varcharDateTime,13,2)) <> 1
THEN 0 ELSE 1 END AS valid, varcharDateTime
FROM #testEnvironment) a
WHERE valid = 1
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
But I prefer CLR when I have to do something like this. The RegEx function in .Net is much faster at validating this sort of thing (I'll knock one up later on if no-one comes up with a faster solution)
November 29, 2011 at 5:31 am
rashid_alavi (11/29/2011)
hi All,I Dont want to change on the DB , i want to have that display as YYYY/MM/DD when i want to generate reports for the application reports, the database is MSSQL Server 2005 , i am not getting the right syntax to put it accross.
appreciate if anyone can give idea on this.
You still haven't said whether you want to display the time as 24 hour time or AM/PM. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2011 at 5:36 am
Cadavre (11/29/2011)
But I prefer CLR when I have to do something like this. The RegEx function in .Net is much faster at validating this sort of thing (I'll knock one up later on if no-one comes up with a faster solution)
Sometimes, that's true. Sometimes, it's not. Matt Miller and I did some extreme testing (admittedly, several years ago) between the use of RegEx CLRs and validating data in T-SQL. There were many things where T-SQL beat the tar out of the CLRs.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2011 at 5:44 am
Jeff Moden (11/29/2011)
Cadavre (11/29/2011)
But I prefer CLR when I have to do something like this. The RegEx function in .Net is much faster at validating this sort of thing (I'll knock one up later on if no-one comes up with a faster solution)Sometimes, that's true. Sometimes, it's not. Matt Miller and I did some extreme testing (admittedly, several years ago) between the use of RegEx CLRs and validating data in T-SQL. There were many things where T-SQL beat the tar out of the CLRs.
Agreed, but in my testing I've found the reverse to be true more often. However CLR comes with extra problems, in that so far 100% of the DBAs I've worked under have refused to allow CLRs anywhere near their server 😀
November 29, 2011 at 6:24 am
preferrable 24 hours time. and its ok if you can provide 12 hours time as well
November 29, 2011 at 8:03 am
rashid_alavi (11/29/2011)
hi All,I Dont want to change on the DB , i want to have that display as YYYY/MM/DD when i want to generate reports for the application reports, the database is MSSQL Server 2005 , i am not getting the right syntax to put it accross.
appreciate if anyone can give idea on this.
There is a T-SQL function called CONVERT which can accept a datetime value and return a formatted string. This is problematic, because your initial string value of YYYYMMDDHHMMSS must first be converted into a datetime, but before that, the HHMMSS must be stuffed with colons. Yuck!
There is also a function called STUFF which can embed a string (like a colon or slash) into another string. You transform your "date" string using a combination of CONVERT and STUFF, or just use a combination of several STUFFs.
Here are a couple of examples:
declare @mytable table (mydate varchar(14));
insert into @mytable ( mydate ) values ('20111107070115');
select
stuff(stuff(substring(mydate,1,8),5,0,'/'),8,0,'/') as mydate1,
replace(convert(varchar(22),cast(stuff(stuff(stuff(
mydate,9,0,' '),12,0,':'),15,0,':') as datetime),120),'-','/') as mydate2
from @mytable;
mydate1 mydate2
---------- -------------------
2011/11/07 2011/11/07 07:01:15
You can perform this transform in the SELECT statement, or as someone previously suggested, implement it as a computed column within the table itself. A computed column would avoid the possibility of having to put the transform in multiple SELECT statements, if several different reports need to use it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 29, 2011 at 8:17 am
Here's a few different methods.
BEGIN TRAN
SET NOCOUNT ON
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
varcharDateTime
INTO #testEnvironment
FROM (SELECT TOP 999999
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),(RAND(CHECKSUM(NEWID())) * 30000) + CAST('1945' AS DATETIME),20),'-',''),':',''),' ','') AS varcharDateTime
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
UNION ALL
SELECT '20190231191114' --INVALID DATE!!
) a
--HOLDER TO TAKE THE DISPLAY TIME OUT OF THE EQUATION
DECLARE @TEST VARCHAR(100)
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT @TEST = COUNT(*) FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CONVERT WITH CASE ==========' --WILL PICK UP ON THE INVALID DATE I HAVE ADDED
SET STATISTICS TIME ON
SELECT @TEST = formattedVarcharDateTime
FROM (SELECT SUBSTRING(varcharDateTime,1,4) + '/' + SUBSTRING(varcharDateTime,5,2) + '/' + SUBSTRING(varcharDateTime,7,2) + ' ' +
SUBSTRING(varcharDateTime,9,2) + ':' + SUBSTRING(varcharDateTime,11,2) + ':' + SUBSTRING(varcharDateTime,13,2) AS formattedVarcharDateTime,
CASE WHEN varcharDateTime NOT LIKE '[1-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-2][0-9][0-5][0-9][0-5][0-9]'
THEN 0
WHEN ISDATE(SUBSTRING(varcharDateTime,1,4) + '/' + SUBSTRING(varcharDateTime,5,2) + '/' + SUBSTRING(varcharDateTime,7,2) + ' ' +
SUBSTRING(varcharDateTime,9,2) + ':' + SUBSTRING(varcharDateTime,11,2) + ':' + SUBSTRING(varcharDateTime,13,2)) <> 1
THEN 0 ELSE 1 END AS valid, varcharDateTime
FROM #testEnvironment) a
WHERE valid = 1
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CONVERT WITH CLR ==========' --WILL PICK UP ON THE INVALID DATE I HAVE ADDED
SET STATISTICS TIME ON
SELECT @TEST = SUBSTRING(varcharDateTime,1,4) + '/' + SUBSTRING(varcharDateTime,5,2) + '/' + SUBSTRING(varcharDateTime,7,2) + ' ' +
SUBSTRING(varcharDateTime,9,2) + ':' + SUBSTRING(varcharDateTime,11,2) + ':' + SUBSTRING(varcharDateTime,13,2)
FROM (SELECT dbo.ReplaceMatch(varcharDateTime,
'^([1-9]\d{3}((0[1-9]|1[012])(0[1-9]|1\d|2[0-8])|(0[13456789]|1[012])(29|30)|(0[13578]|1[02])31)|(([2-9]\d)(0[48]|[2468][048]|[13579][26])|(([2468][048]|[3579][26])00))0229)(([0-1][0-9])|(2[0-3]))([0-5][0-9])([0-5][0-9])',
varcharDateTime) AS varcharDateTime
FROM #testEnvironment) a
WHERE varcharDateTime IS NOT NULL
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CONVERT WITH REPLACE ==========' --WILL NOT PICK UP ON THE INVALID DATE I HAVE ADDED
SET STATISTICS TIME ON
SELECT @TEST = REPLACE(CONVERT(VARCHAR(22), CAST(STUFF(STUFF(STUFF(
varcharDateTime, 9, 0, ' '), 12, 0, ':'), 15, 0, ':') AS DATETIME), 120), '-', '/')
FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CONVERT WITH REPLACE AND CLR ==========' --WILL PICK UP ON THE INVALID DATE I HAVE ADDED
SET STATISTICS TIME ON
SELECT @TEST = REPLACE(CONVERT(VARCHAR(22), CAST(STUFF(STUFF(STUFF(
varcharDateTime, 9, 0, ' '), 12, 0, ':'), 15, 0, ':') AS DATETIME), 120), '-', '/')
FROM (SELECT dbo.ReplaceMatch(varcharDateTime,
'^([1-9]\d{3}((0[1-9]|1[012])(0[1-9]|1\d|2[0-8])|(0[13456789]|1[012])(29|30)|(0[13578]|1[02])31)|(([2-9]\d)(0[48]|[2468][048]|[13579][26])|(([2468][048]|[3579][26])00))0229)(([0-1][0-9])|(2[0-3]))([0-5][0-9])([0-5][0-9])',
varcharDateTime) AS varcharDateTime
FROM #testEnvironment) a
WHERE varcharDateTime IS NOT NULL
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CONVERT WITH REPLACE AND CASE ==========' --WILL PICK UP ON THE INVALID DATE I HAVE ADDED
SET STATISTICS TIME ON
SELECT @TEST = REPLACE(CONVERT(VARCHAR(22), CAST(STUFF(STUFF(STUFF(
varcharDateTime, 9, 0, ' '), 12, 0, ':'), 15, 0, ':') AS DATETIME), 120), '-', '/')
FROM (SELECT varcharDateTime,
CASE WHEN varcharDateTime NOT LIKE '[1-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-2][0-9][0-5][0-9][0-5][0-9]'
THEN 0
WHEN ISDATE(SUBSTRING(varcharDateTime,1,4) + '/' + SUBSTRING(varcharDateTime,5,2) + '/' + SUBSTRING(varcharDateTime,7,2) + ' ' +
SUBSTRING(varcharDateTime,9,2) + ':' + SUBSTRING(varcharDateTime,11,2) + ':' + SUBSTRING(varcharDateTime,13,2)) <> 1
THEN 0 ELSE 1 END AS valid
FROM #testEnvironment) a
WHERE valid = 1
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
ROLLBACK
========== BASELINE ==========
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 70 ms.
================================================================================
========== CONVERT WITH CASE ==========
SQL Server Execution Times:
CPU time = 7969 ms, elapsed time = 8018 ms.
================================================================================
========== CONVERT WITH CLR ==========
SQL Server Execution Times:
CPU time = 16016 ms, elapsed time = 16539 ms.
================================================================================
========== CONVERT WITH REPLACE ==========
Msg 242, Level 16, State 3, Line 59
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SQL Server Execution Times:
CPU time = 4516 ms, elapsed time = 4531 ms.
================================================================================
========== CONVERT WITH REPLACE AND CLR ==========
SQL Server Execution Times:
CPU time = 22141 ms, elapsed time = 22725 ms.
================================================================================
========== CONVERT WITH REPLACE AND CASE ==========
SQL Server Execution Times:
CPU time = 11516 ms, elapsed time = 11535 ms.
================================================================================
I could do with tuning all of it (especially the CLR), but it should be enough to get you started.
November 29, 2011 at 10:33 am
I Dont want to change on the DB , i want to have that display as YYYY/MM/DD when i want to generate reports for the application reports, the database is MSSQL Server 2005 , i am not getting the right syntax to put it accross.
One way to do this, would be to use a CAST function to convert the string to a date, like so
CAST([DateString] AS DATETIME)
CAST will take a string in any valid date and time format, and convert it to a date-time field. Then embed that function in a CONVERT function to display the data the way you want it. CONVERT allows you to specify a style for displaying date and time strings. The style you have specified is pretty close to the ODBC canonical style (YYYY-MM-DD hh:mm:ss 24 hour format). That is style 120 in T-SQL. So, the final function would look like this.
CONVERT(VARCHAR(19), CAST([DateString] AS DATETIME), 120)
As the other posters have said, your main problem will be dealing with improperly formatted data. However, this function should do as you have requested.
November 29, 2011 at 10:42 am
David Moutray (11/29/2011)
I Dont want to change on the DB , i want to have that display as YYYY/MM/DD when i want to generate reports for the application reports, the database is MSSQL Server 2005 , i am not getting the right syntax to put it accross.
One way to do this, would be to use a CAST function to convert the string to a date, like so
CAST([DateString] AS DATETIME)CAST will take a string in any valid date and time format, and convert it to a date-time field. Then embed that function in a CONVERT function to display the data the way you want it. CONVERT allows you to specify a style for displaying date and time strings. The style you have specified is pretty close to the ODBC canonical style (YYYY-MM-DD hh:mm:ss 24 hour format). That is style 120 in T-SQL. So, the final function would look like this.
CONVERT(VARCHAR(19), CAST([DateString] AS DATETIME), 120)As the other posters have said, your main problem will be dealing with improperly formatted data. However, this function should do as you have requested.
The wrinkle is that "20111107070115" won't CAST to datetime as is. He has to first STUFF a space before the time portion and some colons between the HH:MM:SS.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply