September 17, 2015 at 7:06 am
Jeff Moden (9/16/2015)
Sean Lange (9/16/2015)
drew.allen (9/16/2015)
Try the FORMAT function. It was added in SQL 2012.
SELECT FORMAT(vhrgdt, '0000-00-00') + ' ' + FORMAT(vhrgtm, '00:00:00.000'), *
FROM #Something s
Drew
Thanks Drew. I don't use FORMAT very often but that is certainly a mountain simpler than what I was doing. The code I had worked but was just awful because I basically did all that formatting manually. :w00t:
DO NOT USE FORMAT!!!! PERIOD!!!! It takes 44 times longer than either CAST or CONVERT. I suspect that it's a piece of really bad Regex behind the scenes.
Yeah I noticed that while the code is a lot cleaner it took quite a bit of time to run. The ugly piece of this is that this is going to run for an ETL process and is part of the where predicate so I can determine which rows to return. The table is small right now because the system is not yet in production but it isn't likely to get too huge. Maybe 100 or so new rows each week is all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2015 at 7:15 am
My guess is that convert-int-to-string, massage with SubString, convert-String-To-Date (or Time) is going to be much slower than something purely arithmetic.
September 17, 2015 at 7:25 am
tripleAxe (9/17/2015)
Hi Sean, was your code as nasty looking as mine below?
SELECT
DATEADD(SECOND, CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)),6),1,2)) * 60 * 60 + CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)),6),3,2)) * 60 + SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)),6),5,2), CONVERT(DATETIME, CONVERT(VARCHAR(8), VHRGDT),113)),
DesiredDateTimeValue
FROM #Something
This one wins as the most verbose way of getting the results so far. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2015 at 7:29 am
In case anybody is interested I compiled all the suggestions into a single query just so you can see them side by side. 😉
select DesiredDateTimeValue
, cast(FORMAT(VHRGDT, '0000-00-00') + ' ' + FORMAT(VHRGTM, '00:00:00.000') as datetime) as Drew
, DATEADD(SECOND, CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)), 6), 1, 2)) * 60 * 60 + CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)), 6), 3, 2)) * 60 + SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)), 6), 5, 2), CONVERT(DATETIME, CONVERT(VARCHAR(8), VHRGDT), 113)) as tripleAxe
, CONVERT( DATETIME, CONVERT(VARCHAR(8), VHRGDT), 113) + CONVERT(DATETIME, STUFF(STUFF(RIGHT('0'+CONVERT(VARCHAR(6), VHRGTM), 6), 3, 0, ':'), 6,0,':'), 108) as tripleAxe2
, msdb.dbo.agent_datetime(VHRGDT,VHRGTM) as Lynn
, CONVERT(datetime, RTRIM(VHRGDT)) + (VHRGTM * 9 + VHRGTM % 10000 * 6 + VHRGTM % 100 * 10) / 216e4 as Kristen
, DateTimeFromParts(VHRGDT / 10000, (VHRGDT % 10000) / 100, VHRGDT % 100 , VHRGTM / 10000, (VHRGTM / 100) % 100, VHRGTM % 100, 0) as Kristen2
, CONVERT(DATETIME,CONVERT(VARCHAR(8),VHRGDT,0) + CHAR(32) + STUFF(STUFF(STUFF(CONVERT(VARCHAR(8) ,10000000 + VHRGTM,0),5,0,CHAR(58)),8,0,CHAR(58)),1,2,''),112) as Ed
, CAST(CAST(CAST(STR(VHRGDT) AS DATE) AS CHAR(10)) + ' ' + STUFF(STUFF(RIGHT('0' + CAST(VHRGTM AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) as Sean
from #Something
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2015 at 7:32 am
Ed Wagner (9/17/2015)
To test it, I created a table with column names that don't remind me of days past. Sean, I think you know what I'm talking about. 😉
I do!!! In fact I specifically left those damn awful column names just for you. Had it been anything else I would have changed those names to prevent others from having nightmares. The table and column names are like reading an eye chart in this system. I would rather peel M&Ms in the desert than work with this system.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2015 at 7:39 am
Sean Lange (9/17/2015)
I compiled all the suggestions into a single query just so you can see them side by side.
All you need now is to be able to SET STATISTICS TIME ON per column 🙂
(I'm probably now going to find that this is possible ...)
September 17, 2015 at 7:47 am
Someone had mentioned that the date/time conversions can be expensive. Depending on what you're needing to do with the dates and times once they're converted, it might make sense just to create a set of date and time lookup tables. Yes, it's now an additional set of joins, but the tables are relatively small, easily cached in buffer, and they can contain other useful pre-computed columns. Maybe it isn't practical for your given scenario, but I just thought I'd toss this option out there to try on for size.
create table DateList
(
DInt int not null primary key,
DDate date not null,
IsHoliday bit not null,
IsWeekDay bit not null,
...
...
);
create table TimeList
(
TInt int not null primary key,
TTIme time not null,
IsOfficeHours bit not null,
WorkShift tinyint not null,
...
...
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 17, 2015 at 7:49 am
Can I join the party? 😀
Why are you complaining about a company who follows MS examples? If MS stores dates and times like this it should be right. :hehe:
Now being serious, here's my formula which is very similar to the one Eirikur posted previously.
select *, CAST( CAST( VHRGDT AS char(9)) + STUFF( STUFF( RIGHT( VHRGTM + 1000000, 6), 5, 0, ':'), 3, 0, ':') AS datetime)
from #Something
The arithmetic formula posted by Kristen is awesome, but I'm not sure if I would use it. It has some accuracy problems as well.
September 17, 2015 at 7:53 am
Eric M Russell (9/17/2015)
Someone had mentioned that the date/time conversions can be expensive. Depending on what you're needing to do with the dates and times once they're converted, it might make sense just to create a set of date and time lookup tables. Yes, it's now an additional set of joins, but the tables are relatively small, easily cached in buffer, and they can contain other useful pre-computed columns. Maybe it isn't practical for your given scenario, but I just thought I'd toss this option out there to try on for size.
create table DateList
(
DInt int not null primary key,
DDate date not null,
IsHoliday bit not null,
IsWeekDay bit not null,
...
...
);
create table TimeList
(
TInt int not null primary key,
TTIme time not null,
IsOfficeHours bit not null,
WorkShift tinyint not null,
...
...
);
A calendar table is way overkill for this. I am just creating an ETL process and this is for a single query that gets all rows that were created since the last run. All I need is the where predicate and the version I came up with is entirely too verbose for my liking. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2015 at 8:10 am
Kristen-173977 (9/16/2015)
... but somewhere on my travels I saw this which satisfies my perception that an arithmetic algorithm will perform better than a string one, but the formula is a bit Smoke & Mirrors to me
CONVERT(datetime, RTRIM(VHRGDT))
+ (VHRGTM * 9 +VHRGTM % 10000 * 6 + VHRGTM % 100 * 10)
/ 216e4
This is pretty cool but there is no way I am putting this into production. The amount of comments around this so somebody else can maintain it rather offsets the benefit.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2015 at 8:14 am
Man o' man... guess I need to find out what the hell's the matter with my Dev box at work. Here are the run results of Lynn's code from that box... the bad code duration is nearly doubled and the good code duration is nearly tripled.
---FORMAT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 89 ms.
SQL Server Execution Times:
CPU time = 42578 ms, elapsed time = 53452 ms.
---CONVERT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 922 ms, elapsed time = 911 ms.
---FORMAT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 42188 ms, elapsed time = 50302 ms.
---CONVERT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 937 ms, elapsed time = 930 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2015 at 8:17 am
Jeff Moden (9/17/2015)
Man o' man... guess I need to find out what the hell's the matter with my Dev box at work. Here are the run results of Lynn's code from that box... the bad code duration is nearly doubled and the good code duration is nearly tripled.
---FORMAT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 89 ms.
SQL Server Execution Times:
CPU time = 42578 ms, elapsed time = 53452 ms.
---CONVERT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 922 ms, elapsed time = 911 ms.
---FORMAT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 42188 ms, elapsed time = 50302 ms.
---CONVERT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 937 ms, elapsed time = 930 ms.
Jeff, how many rows in your test set?
September 17, 2015 at 8:27 am
Ed Wagner (9/17/2015)
Jeff Moden (9/17/2015)
Man o' man... guess I need to find out what the hell's the matter with my Dev box at work. Here are the run results of Lynn's code from that box... the bad code duration is nearly doubled and the good code duration is nearly tripled.
---FORMAT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 89 ms.
SQL Server Execution Times:
CPU time = 42578 ms, elapsed time = 53452 ms.
---CONVERT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 922 ms, elapsed time = 911 ms.
---FORMAT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 42188 ms, elapsed time = 50302 ms.
---CONVERT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 937 ms, elapsed time = 930 ms.
Jeff, how many rows in your test set?
Does Jeff ever test with anything less than a million or more rows??
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2015 at 8:29 am
Sean Lange (9/17/2015)
The table is small right now because the system is not yet in production but it isn't likely to get too huge. Maybe 100 or so new rows each week is all.
Heh... famous last words. 😛
Jayanth Kurup made a suggestion above that made me think of an alternate. You say that you can't modify the table but, as many such projects go, do you really "only" mean that you can't change the existing columns? If that's true, then add a persisted computed column with the formula (might be a call to a well written scalar function) to do the conversion. At least that way your queries stand a chance of using an index during temporal searches.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2015 at 8:33 am
Sean Lange (9/17/2015)
Ed Wagner (9/17/2015)
Jeff Moden (9/17/2015)
Man o' man... guess I need to find out what the hell's the matter with my Dev box at work. Here are the run results of Lynn's code from that box... the bad code duration is nearly doubled and the good code duration is nearly tripled.
---FORMAT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 89 ms.
SQL Server Execution Times:
CPU time = 42578 ms, elapsed time = 53452 ms.
---CONVERT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 922 ms, elapsed time = 911 ms.
---FORMAT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 42188 ms, elapsed time = 50302 ms.
---CONVERT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 937 ms, elapsed time = 930 ms.
Jeff, how many rows in your test set?
Does Jeff ever test with anything less than a million or more rows??
Touche. If the code performs well over 1M rows, it should do fine over 100 rows. Testing with 1M tends to point out weakness.
Viewing 15 posts - 16 through 30 (of 62 total)
You must be logged in to reply to this topic. Login to reply