February 23, 2015 at 11:14 am
I have a column that is a datatype varchar(12) and is displaying dates as MMM DD YYY (i.e. "Jul 11 2014"). I need to get it into a yyyymmdd format (i.e. "20140711"). I have tried to cast/convert/substring and cannot get. I get stuck with dashes(i.e. "2014-07-11") when using either of the following codes below.
select (cast(substring(A1.Coll_DT,8,4)+substring(A1.Coll_DT,1,3)+substring(A1.Coll_DT,5,2)as DATE))
from a1
or
select (CONVERT(date,a1.coll_dt,112)) from A1
I can't figure out how to get it into an INT datatype. I thought I could use 'select CAST(convert(date,a1.coll_dt,112)as int) from A1', but get a message saying it's not allowed.
Any help would be extremely appreciated
February 23, 2015 at 11:26 am
Here is one way.
declare @dt varchar(12) = 'Jul 11 2014';
with dt
as (
select dt1 = cast(substring(@dt, 8, 4) + substring(@dt, 1, 3) + substring(@dt, 5, 2) as date)
)
select year(dt.dt1) * 10000 + month(dt.dt1) * 100 + day(dt.dt1)
from dt
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 23, 2015 at 11:41 am
February 23, 2015 at 11:59 am
Luis Cazares (2/23/2015)
There's no need to over complicate it.Actually, if you're inserting into an integer column, you don't need the CAST() from this example as it will be implicitly converted.
declare @dt varchar(12) = 'Jul 11 2014';
SELECT CAST( CONVERT( char(8), CONVERT( date, @dt, 100), 112) AS int)
Cool. Same execution plans for both.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 23, 2015 at 12:18 pm
Thank you!
February 23, 2015 at 12:28 pm
ok...disregard. I'm all set. I just need help with copy/paste. THANK YOU. you guys rock
February 23, 2015 at 12:34 pm
Phil Parkin (2/23/2015)
Luis Cazares (2/23/2015)
There's no need to over complicate it.Actually, if you're inserting into an integer column, you don't need the CAST() from this example as it will be implicitly converted.
declare @dt varchar(12) = 'Jul 11 2014';
SELECT CAST( CONVERT( char(8), CONVERT( date, @dt, 100), 112) AS int)
Cool. Same execution plans for both.
But not the same execution times. Putting a quick speed test, it seems that the multiple conversion option is much faster.
Here's what I did to test.
PRINT 'Create the sample data';
--===== Declare some obviously named variables
DECLARE @NumberOfRows INT,
@StartDate DATETIME,
@EndDate DATETIME,
@Days INT --range
;
--===== Preset the variables to known values
SELECT @NumberOfRows = 1000000,
@StartDate = '2010', --Inclusive
@EndDate = '2020', --Exclusive
@Days = DATEDIFF(dd,@StartDate,@EndDate)
SELECT TOP (@NumberOfRows)
CONVERT( char( 11 ),ABS(CHECKSUM(NEWID())) % @Days + @StartDate, 100 ) Stringdate
INTO Stringdates
FROM sys.all_columns a, sys.all_columns b;
PRINT 'Multiple Conversions';
SET STATISTICS TIME ON;
SELECT CAST( CONVERT( char( 8 ), CONVERT( date, Stringdate, 100 ), 112 )AS int )Mydate
INTO #Test1
FROM Stringdates;
SET STATISTICS TIME OFF;
PRINT '---------------------------------------';
PRINT 'Dividing the date';
SET STATISTICS TIME ON;
WITH Dt
AS ( SELECT Dt1 = CAST( SUBSTRING( Stringdate, 8, 4 )
+ SUBSTRING( Stringdate, 1, 3 )
+ SUBSTRING( Stringdate, 5, 2 )AS date )
FROM Stringdates )
SELECT YEAR( Dt.Dt1 ) * 10000 + MONTH( Dt.Dt1 ) * 100 + DAY( Dt.Dt1 )Mydate INTO #Test2
FROM Dt;
SET STATISTICS TIME OFF;
GO
DROP TABLE #Test1;
DROP TABLE #Test2;
GO
DROP TABLE Stringdates;
GO
These are the results:
Create the sample data
(1000000 row(s) affected)
Multiple Conversions
SQL Server Execution Times:
CPU time = 4172 ms, elapsed time = 4458 ms.
(1000000 row(s) affected)
---------------------------------------
Dividing the date
SQL Server Execution Times:
CPU time = 11313 ms, elapsed time = 11719 ms.
(1000000 row(s) affected)
I know the test is not perfect but it gives an idea on the difference.
February 23, 2015 at 12:51 pm
Good work Luis. I'm a little surprised at the result, given that there are so many data-type conversions, but the proof is there clear enough.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 23, 2015 at 4:53 pm
I believe that there is another way.
PRINT 'Create the sample data';
--===== Declare some obviously named variables
DECLARE @NumberOfRows INT,
@StartDate DATETIME,
@EndDate DATETIME,
@Days INT --range
;
--===== Preset the variables to known values
SELECT @NumberOfRows = 1000000,
@StartDate = '2010', --Inclusive
@EndDate = '2020', --Exclusive
@Days = DATEDIFF(dd,@StartDate,@EndDate)
SELECT TOP (@NumberOfRows)
CONVERT( char( 11 ),ABS(CHECKSUM(NEWID())) % @Days + @StartDate, 100 ) Stringdate
INTO Stringdates
FROM sys.all_columns a, sys.all_columns b;
PRINT 'Multiple Conversions';
SET STATISTICS TIME ON;
SELECT CAST( CONVERT( char( 8 ), CONVERT( date, Stringdate, 100 ), 112 )AS int )Mydate
INTO #Test1
FROM Stringdates;
SET STATISTICS TIME OFF;
PRINT '---------------------------------------';
PRINT 'Dividing the date';
SET STATISTICS TIME ON;
WITH Dt
AS ( SELECT Dt1 = CAST( SUBSTRING( Stringdate, 8, 4 )
+ SUBSTRING( Stringdate, 1, 3 )
+ SUBSTRING( Stringdate, 5, 2 )AS date )
FROM Stringdates )
SELECT YEAR( Dt.Dt1 ) * 10000 + MONTH( Dt.Dt1 ) * 100 + DAY( Dt.Dt1 )Mydate INTO #Test2
FROM Dt;
SET STATISTICS TIME OFF;
PRINT '---------------------------------------';
PRINT 'Another way';
SET STATISTICS TIME ON;
SELECT dt=0+CONVERT(CHAR(8), CAST(Stringdate AS DATE),112) INTO #Test3
FROM Stringdates;
SET STATISTICS TIME OFF;
GO
DROP TABLE #Test1;
DROP TABLE #Test2;
DROP TABLE #Test3;
GO
DROP TABLE Stringdates;
GO
Results:
Create the sample data
(1000000 row(s) affected)
Multiple Conversions
SQL Server Execution Times:
CPU time = 2812 ms, elapsed time = 2795 ms.
(1000000 row(s) affected)
---------------------------------------
Dividing the date
SQL Server Execution Times:
CPU time = 6578 ms, elapsed time = 6594 ms.
(1000000 row(s) affected)
---------------------------------------
Another way
SQL Server Execution Times:
CPU time = 2500 ms, elapsed time = 2521 ms.
(1000000 row(s) affected)
Nice test harness Luis!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 24, 2015 at 12:16 am
Nice one Dwain. Interestingly, on my machine (desktop PC running W7 64-bit and SQL Server 2014 Dev), I get different results:
Create the sample data
(1000000 row(s) affected)
Multiple Conversions
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1682 ms, elapsed time = 385 ms.
(1000000 row(s) affected)
---------------------------------------
Dividing the date
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4929 ms, elapsed time = 739 ms.
(1000000 row(s) affected)
---------------------------------------
Another way
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1919 ms, elapsed time = 328 ms.
(1000000 row(s) affected)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 24, 2015 at 12:36 am
Phil Parkin (2/24/2015)
Nice one Dwain. Interestingly, on my machine (desktop PC running W7 64-bit and SQL Server 2014 Dev), I get different results:
Create the sample data
(1000000 row(s) affected)
Multiple Conversions
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1682 ms, elapsed time = 385 ms.
(1000000 row(s) affected)
---------------------------------------
Dividing the date
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4929 ms, elapsed time = 739 ms.
(1000000 row(s) affected)
---------------------------------------
Another way
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1919 ms, elapsed time = 328 ms.
(1000000 row(s) affected)
Different indeed, but I note the elapsed time ranking remains the same, even though CPU ranking wasn't. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 24, 2015 at 6:11 am
It works for me when I first cast the string as date and then wrap a convert function around that.
PRINT convert(char(8),cast('Jul 11 2014' as date),112);
20140711
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 24, 2015 at 6:16 am
FYI:
If you're struggling with users entering "date" values into a varchar column in required format, the following check constraint enforces insertion of values as YYYYMMDD.
create table foo
(
foo_date varchar(30) not null
constraint ck_foo_date_yyyymmdd
check (foo_date = convert(char(8),cast(foo_date as date),112))
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 24, 2015 at 2:42 pm
I for one like the yyyy-mm-dd notation. In other countries a '20140711' could be interpreted as Nov 07, 2014. If you changed the default language setting on your machine then that could do it. Just something to keep in mind if you keep track of events that happen globally (like user clicks) and they get shared globally.
----------------------------------------------------
February 24, 2015 at 2:56 pm
MMartin1 (2/24/2015)
I for one like the yyyy-mm-dd notation. In other countries a '20140711' could be interpreted as Nov 07, 2014. If you changed the default language setting on your machine then that could do it. Just something to keep in mind if you keep track of events that happen globally (like user clicks) and they get shared globally.
For date strings, YYYYMMDD is ISO standard, and 20140711 should always be converted to July 11, 2014 regardless of localization settings or RDMS platform.
However, 2014-07-11 assumes YYYY-MM-DD only in the US and maybe few other places and is subject to interpretation.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply