August 14, 2012 at 11:01 am
Hello Everyone,
I have an sql table which stores dates from some other system as text.
Eg.: DD/MM/YYYY ,31/12/2012.
I have made a query which fetches these dates and put them in a temp table and i have declared varchar(10) as Datatype for this coloumn.
But i want this coloumn to be in date data type.
I need this because i am using this query in SSRS and when i export the result to excel it needs to be in Date DT.
Thanks
Regards
Ravi T
August 14, 2012 at 11:03 am
Can't you change the data type of the column in your temp table to accomodate?
Mark
August 14, 2012 at 11:27 am
Try something like this:
select CONVERT(date, mydate, 103)
from mytable
August 14, 2012 at 12:01 pm
Hi Mark,
yes i can do that but,how will sql know that my text is in dd/mm/yyyy format and not in mm/dd/yyyy or smthing else.
rookie here
regards
Ravi T
August 14, 2012 at 12:04 pm
santa326 (8/14/2012)
Hi Mark,yes i can do that but,how will sql know that my text is in dd/mm/yyyy for and not in mm/dd/yyyy or smthing else.
regards
Ravi T
The third value in the convert statement tells SQL Server the string value of the data/time is in dd/mm/yyyy format.
For more information, read this:
August 16, 2012 at 10:37 pm
HI all,
Ive hit another wall!!!!!!!!.......i have another data set which has date stored as text and the format is dd/mm/yy.Now how do i go about converting this into date datatype?
I have gone through few date time system..n i dint find any number for this type of date.
Regards
Ravi T
August 16, 2012 at 11:39 pm
Instead of 103 use 03
DECLARE @D CHAR(10) = '17/08/12'
SELECT CONVERT(date, @D, 03)
--Vadim R.
August 17, 2012 at 3:01 am
rVadim (8/16/2012)
Instead of 103 use 03
DECLARE @D CHAR(10) = '17/08/12'
SELECT CONVERT(date, @D, 03)
03? That is old ambulance number in Russia, you can use just 3 ...:hehe:
Actually, SQL can convert '17/08/12' and '17/08/2012' into date without help of the third (dateformat) parameter. You are facing the issue due to the default format on your server is in American style (mm/dd/yyy). You can easily change it any time to whatever you like. The following example will explain it to you in a more visual way:
SET DATEFORMAT DMY
DECLARE @D CHAR(10) = '17/08/12'
SELECT CONVERT(date, @D)
SET @D = '17/08/2012'
SELECT CONVERT(date, @D)
GO
SET DATEFORMAT MDY
DECLARE @D CHAR(10) = '8/17/12'
SELECT CONVERT(date, @D)
SET @D = '08/17/2012'
SELECT CONVERT(date, @D)
GO
August 17, 2012 at 9:14 pm
Eugene Elutin (8/17/2012)[hr03? That is old ambulance number in Russia, you can use just 3 ...:hehe:
Oh yea, 03 ?????? ??????. Long time ago, in another life...
--Vadim R.
August 18, 2012 at 9:23 pm
CELKO (8/18/2012)
CAST (SUBSTRING (IN_STR, 7, 4)+'-'+SUBSTRING (IN_STR, 3, 2))+'-'
+SUBSTRING (IN_STR, 1, 2)
AS DATE)
cut up the string and put the Fields into ISO-8601 formatm then cast to DATE.
Really, why do all the string spliting and concatenation when the a) the text string is already in dd/mm/yy format, and b) you can use the format codes (3 or 103) in the convert statement anyway? A lot of extra work for no real value.
August 20, 2012 at 5:04 am
1. CONVERT() is proprietary and will not port; substring and concatenation are Standard.
Port to where? SYBASE? DBASE? MongoDB? Mars? Alpha Centauri?
Do you seriously think that it would help much when porting any serious SQL Server database into something absolutely different? Do you often hear about projects porting SQL into something else? (The key word is "often"). In my, around 20 years of experience it was once and it was into Oracle, where using "proprietary" T-SQL functions was the very last problem, compare with others...
CONVERT is so MS T-SQL, no one in their right mind will believe that it will be dropped "in the next version".
Will you advise against using new non-standard FORMAT function in SQL2012?
2. CONVERT() is an expensive string formatting function, with all those old Sybase options; substring and concatenation are cheap, simple and fast.
Fast? Can you prove, please? So far all test ever been done on this did show no difference.
At the end, to convert string datatype into date datatype, engine uses CASTing in both cases! CONVERT does manipulation on string for you based on the format code, and it is in no way slower than multiple string concatenation and "substringing" in T-Sql (you know well, that behind of scene, sql uses immutable strings for this sort of operations - so, they not very fast...).
Simple? One function instead of "substringed" noodles - yeah, that is mater of taste...
3. Neither of those codes returns the ISO-8601 display format used in ANSI/ISO Standard SQL -- you want 126.
If I'm right the OP wanted to convert string to date, he didn't asked for any particular display format. You cannot guess what this data will be used for. I would think he is loading the table, so he doesn't care about displaying it at this point.
4. We want to CAST ( xxx AS DATE) so we can do temporal math with it, so get to the goal in one step, not two.
Here I'm lost a bit. You are saying that the way you shown it happens in one step, but using CONVERT means doing it in two steps? May I ask how do you count "steps"? :hehe:
August 20, 2012 at 9:35 am
CELKO (8/19/2012)
why do all the string spliting and concatenation when the a) the text string is already in dd/mm/yy format, and b) you can use the format codes (3 or 103) in the convert statement anyway?
1. CONVERT() is proprietary and will not port; substring and concatenation are Standard.
The idea of always writing 100% portable code is an academic fallacy. I am sorry but I am going to write code using the all the tools available to me to provide my employer with the best possible solution. I have yet to work for an employer that is concerned with the possibility of having to port their database from one RDBMS environment to another on any regular basis.
2. CONVERT() is an expensive string formatting function, with all those old Sybase options; substring and concatenation are cheap, simple and fast.
Okay, now you are making a claim that one method is faster than another yet fail to back it up. I was too tired last night to write up a formal test suite to check your hypthosis but did do a short informal test. Sorry to burst to bubble here, but using the CONVERT function, written CONVERT(DATE,MyDateStr,103), ran faster than the SUBSTRING/CONCATENATE/CAST, written CAST(SUBSTRING(MyDateStr,7,4) + '-' + SUBSTRING(MyDateStr,4,2) + '-' + SUBSTRING(MyDateStr,1,2) as DATE), using a 1,000,000 row table was actually faster on all but 1 run. I reran the queries 5 times, so that is 80% of the time the CONVERT beat your solution. Hopefully I will have more time in the next couple of days to put together a more formal test suite.
3. Neither of those codes returns the ISO-8601 display format used in ANSI/ISO Standard SQL -- you want 126.
The OP wasn't looking for the ISO-8601 display format. The OP wanted to convert dates currently stored as character strings in dd/mm/yyyy format to the DATE data type. CONVERT can be used for that and using the Date/Time style 103 (or 3) informs the CONVERT function that the date strings provided are in that format.
4. We want to CAST ( xxx AS DATE) so we can do temporal math with it, so get to the goal in one step, not two.
Again, not what the OP was requesting. The OP wanted the values in the DATE data type for export to Excel from an SSRS Report.
August 20, 2012 at 4:38 pm
CELKO (8/18/2012)
CAST (SUBSTRING (IN_STR, 7, 4)+'-'+SUBSTRING (IN_STR, 3, 2))+'-'
+SUBSTRING (IN_STR, 1, 2)
AS DATE)
SET DATEFORMAT DMY
select CAST (SUBSTRING (IN_STR, 7, 4)+'-'
+SUBSTRING (IN_STR, 3, 2))+'-'
+SUBSTRING (IN_STR, 1, 2)
AS DATE)
FROM DatesTable
After returning incorrect dates for first 13 lines it gives this back:
Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
_____________
Code for TallyGenerator
August 20, 2012 at 4:44 pm
Sergiy (8/20/2012)
CELKO (8/18/2012)
CAST (SUBSTRING (IN_STR, 7, 4)+'-'+SUBSTRING (IN_STR, 3, 2))+'-'
+SUBSTRING (IN_STR, 1, 2)
AS DATE)
SET DATEFORMAT DMYselect CAST (SUBSTRING (IN_STR, 7, 4)+'-'
+SUBSTRING (IN_STR, 3, 2))+'-'
+SUBSTRING (IN_STR, 1, 2)
AS DATE)
FROM DatesTable
After returning incorrect dates for first 13 lines it gives this back:
Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Okay, Sergiy, you had me nervous for a bit with this one. Then I took a closer look and I saw what the problem was with Mr. Celko's code.
August 20, 2012 at 5:11 pm
CELKO (8/19/2012)
2. CONVERT() is an expensive string formatting function, with all those old Sybase options; substring and concatenation are cheap, simple and fast.
CREATE TABLE #TEMP (
[DATE] date
)
SET NOCOUNT ON
Go
DECLARE @InitTime datetime, @I int
SET @I = 10 -- repeat the operation several times to get an average performance measure
TRUNCATE TABLE #TEMP
SET @InitTime = GETDATE ()
WHILE @I > 0
BEGIN
INSERT INTO #TEMP
SELECT CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE) [Date]
FROM dbo.Tally AS T
SET @I = @I -1
END
PRINT 'CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)
GO
DECLARE @InitTime datetime, @I int
SET @I = 10
TRUNCATE TABLE #TEMP
SET @InitTime = GETDATE ()
WHILE @I > 0
BEGIN
INSERT INTO #TEMP
SELECT CAST (T.ISO_DATE AS DATE) [Date]
FROM dbo.Tally AS T--, dbo.Tally AS T1
SET @I = @I -1
END
PRINT 'CAST (T.ISO_DATE AS DATE): ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)
GO
DECLARE @InitTime datetime, @I int
SET @I = 10
TRUNCATE TABLE #TEMP
SET @InitTime = GETDATE ()
WHILE @I > 0
BEGIN
INSERT INTO #TEMP
SELECT CONVERT(date, T.ISO_DATE, 112) [Date]
FROM dbo.Tally AS T
SET @I = @I -1
END
PRINT 'CONVERT(date, T.ISO_DATE, 112): ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)
GO
DROP TABLE #Temp
Output of 5 consecutive runs on my desktop machine (no other processes are on the way):
CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:01:007
CAST (T.ISO_DATE AS DATE): 00:00:00:533
CONVERT(date, T.ISO_DATE, 112): 00:00:00:527
CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:00:730
CAST (T.ISO_DATE AS DATE): 00:00:00:530
CONVERT(date, T.ISO_DATE, 112): 00:00:00:527
CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:00:767
CAST (T.ISO_DATE AS DATE): 00:00:00:527
CONVERT(date, T.ISO_DATE, 112): 00:00:00:530
CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:00:703
CAST (T.ISO_DATE AS DATE): 00:00:00:623
CONVERT(date, T.ISO_DATE, 112): 00:00:00:527
CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:00:707
CAST (T.ISO_DATE AS DATE): 00:00:00:560
CONVERT(date, T.ISO_DATE, 112): 00:00:00:530
Turns out, SUBSTRING with concatenations is not that fast. It's the slowest option, after all.
And CONVERT is faster than CAST. Only marginally, but still.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply