how can i convert this date'200702071630+1100' in datetime or in string '20070207'

Question

Post reply

how can i convert this date'200702071630+1100' in datetime or in string '20070207'

Patrick.I

Hall of Fame

Points: 3105
More actions
March 8, 2009 at 4:19 pm

#73520

I have a date column tha looks like this '200702071630+1100'
I only want the date like so:
'20070207'
Tried this below for datetime format
select convert(datetime,convert(char(10),'200702071630+1100',108))
but no luck
I really need it like this '20070207'
but sure if i can get it to datetime then i should be able to get it in string format
Thanks in advance

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

[font="Courier New"]DECLARE @Value AS VARCHAR(255)

SET @Value = '200702071630+1100'

SELECT CONVERT(DATETIME,LEFT(@Value,8))

[/font]

Do you mean that? Output of that statement is "2007-02-07 00:00:00.000". Thanks.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 2

I have a date column tha looks like this '200702071630+1100'
I only want the date like so:
'20070207'
Tried this below for datetime format
select convert(datetime,convert(char(10),'200702071630+1100',108))
but no luck

If all you need is a character string representation of the local date of this date/time string then

SELECT SUBSTRING('200702071630+1100', 1, 8)

or

SELECT CONVERT(char(8), '200702071630+1100')

will return '20070207', and the following will convert the local date of this UTC date/time string to a datetime data type.

SELECT CONVERT(datetime, SUBSTRING('200702071630+1100', 1, 8))

or

SELECT CONVERT(datetime, CONVERT(char(8), '200702071630+1100'))

However, are you sure this is what you want?

Do you want the UTC (Coordinated Universal Time) date/time or a local date/time?

If you need the UTC date/time then your example string actually represents the date/time 2007-02-07 05:30:00. If your date/time string format consistently has the format YYYYMMDDHHMM[+-]HHMM, then the following expression will convert the string to a UTC date/time.

DECLARE @sdt varchar(30)

SELECT @sdt = '200702071630+1100'

SELECT DATEADD(minute,

(CASE SUBSTRING(@sdt, 13, 1) WHEN '-' THEN 1 ELSE -1 END)

* (60 * CONVERT(int, SUBSTRING(@sdt, 14, 2)) + CONVERT(int, SUBSTRING(@sdt, 16, 2)))

+ (60 * CONVERT(int, SUBSTRING(@sdt, 9, 2)) + CONVERT(int, SUBSTRING(@sdt, 11, 2))),

CONVERT(datetime, SUBSTRING(@sdt, 1, 8)))

To convert a datetime data type to the ISO string format YYYYMMDD, use the CONVERT function with style 112.

DECLARE @dt datetime

SELECT @dt = '2007-02-07 16:30:00'

SELECT CONVERT(char(8), @dt, 112)

EDIT: Got the UTC and local datetimes back to front originally - now corrected.

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

The "+1100" indicates the time zone of the date. You may have to substract that as a number of hours and minutes from the date and that will give you the "Universal Date" which is "GMT" without any daylight savings time. Then you need to add your own local time zone offset in hour and minutes to get the correct local time when the event acutally happened.

Or, just do like Mohit suggested if none of that matters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

Heh... dangit, Andrew... you beat me to it. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 5

Hi Jeff,

Yes but I got UTC and local time corrections back to front first time around.

Patrick.I Hall of Fame Points: 3105 More actions · Answer 6

Patrick.I

Hall of Fame

Points: 3105

March 9, 2009 at 8:50 pm

#956527

Thx Guys..

karthik M One Orange Chip Points: 29139 More actions · Answer 7

[font="Verdana"]

Jeff,

I did a small R&D to know which method is good performance wise.

CREATE TABLE TBL

(

N INT,

DATE_TIME VARCHAR(30)

)

insert into TBL

Select n,'200702071630+1100'as DATE_TIME

from tally

I have executed the above script for 11 times. so the total rowcount in TBL is 110,000

then I have executed the below scripts.

set statistics time on

go

set statistics io on

go

#1)

SELECT CONVERT(DATETIME,LEFT(DATE_TIME,8))

FROM TBL

Test1:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 172 ms, elapsed time = 25098 ms.

Test2:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 156 ms, elapsed time = 24960 ms.

Test3:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 156 ms, elapsed time = 24772 ms.

#2)

SELECT CONVERT(datetime, SUBSTRING(DATE_TIME, 1, 8))

FROM TBL

Test1:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 187 ms, elapsed time = 24529 ms.

Test2:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 188 ms, elapsed time = 24583 ms.

Test3:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 312 ms, elapsed time = 25036 ms.

[/font]

Based on the above statistics,substring function will be good. Right?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 8

I also test the below query.

SELECT CONVERT(CHAR(8),DATE_TIME)

FROM TBL

Test1:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 78 ms, elapsed time = 27610 ms.

Test2:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 62 ms, elapsed time = 28384 ms.

Test3:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 78 ms, elapsed time = 28615 ms.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 9

I have also tested the below query.

SELECT CONVERT(DATETIME,CONVERT(CHAR(8),DATE_TIME))

FROM TBL

Test1:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 250 ms, elapsed time = 25135 ms.

Test2:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 2 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 234 ms, elapsed time = 23117 ms.

Test3:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(110000 row(s) affected)

Table 'TBL'. Scan count 1, logical reads 485, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 266 ms, elapsed time = 25077 ms.

shall we consider this method also good?

I want to know how to calculate the cost of LEFT,SUBSTRING and CHAR functions.

I mean how much memory space sql server allocate for these functions? say for example LEFT function 2 bytes something like that.

karthik

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 10

The problem with your tests is that the time to display the results are being included. Also, as you saw in your own testing, converting the date to CHAR(8) is the fastest of these unsafe methods for converting a data. The reason why they are unsafe is that you're counting on a default display method which could change.

If you want to test some of the other string functions for speed, try something like this, which also makes the output a bit easier to decipher...

DROP TABLE JBMTest

GO

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

-- Column "SomeString" consists of 6 delimited groups of 1 to 3 characters

-- Jeff Moden

SELECT TOP 1000000

RowNum = IDENTITY(INT,1,1),

SomeString = CAST(RIGHT(NEWID(),ABS(CHECKSUM(NEWID()))%3+1) +';'

+ RIGHT(NEWID(),ABS(CHECKSUM(NEWID()))%3+1) +';'

+ RIGHT(NEWID(),ABS(CHECKSUM(NEWID()))%3+1)

AS VARCHAR(50))

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1

CROSS JOIN Master.dbo.SysColumns t2

--===== Add a clustered primary key just because

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Display the first 100 rows just to see what they contain

SELECT TOP 100 * FROM dbo.JBMTest

--===== Declare the bit bucket variables used to short circuit the display

DECLARE @BitBucketCHAR VARCHAR(50)

DECLARE @BitBucketINT INT

--===== Start the CPU and duration timers

SET STATISTICS TIME ON

--===== Run the tests... the PRINT statements identify each test in the output

PRINT '==================== No Mod''s (Baseline) ===================='

SELECT @BitBucketCHAR = SomeString

FROM dbo.JBMTest

PRINT '==================== SUBSTRING(1,15) ===================='

SELECT @BitBucketCHAR = SUBSTRING(SomeString,1,15)

FROM dbo.JBMTest

PRINT '==================== LEFT(15) ===================='

SELECT @BitBucketCHAR = LEFT(SomeString,15)

FROM dbo.JBMTest

PRINT '==================== Reverse ===================='

SELECT @BitBucketCHAR = REVERSE(SomeString)

FROM dbo.JBMTest

PRINT '==================== 4 Reverse''s ===================='

SELECT @BitBucketCHAR = REVERSE(REVERSE(REVERSE(REVERSE(SomeString))))

FROM dbo.JBMTest

PRINT '==================== CharIndex ===================='

SELECT @BitBucketINT = CHARINDEX(';',SomeString)

FROM dbo.JBMTest

PRINT '==================== 5 CharIndex''s ===================='

SELECT @BitBucketINT = CHARINDEX(';',SomeString,CHARINDEX(';',SomeString,CHARINDEX(';',SomeString,CHARINDEX(';',SomeString,CHARINDEX(';',SomeString)+1)+1)+1)+1)

FROM dbo.JBMTest

PRINT '==================== LEN() ===================='

SELECT @BitBucketINT = LEN(SomeString)

FROM dbo.JBMTest

--===== Turn of the CPU and duration timers

SET STATISTICS TIME OFF

Here're the results from my box...

[font="Courier New"]

(1000000 row(s) affected)

(100 row(s) affected)

==================== No Mod's (Baseline) ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 750 ms, elapsed time = 905 ms.

==================== SUBSTRING(1,15) ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 890 ms, elapsed time = 1178 ms.

==================== LEFT(15) ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 969 ms, elapsed time = 1270 ms.

==================== Reverse ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 1390 ms, elapsed time = 1532 ms.

==================== 4 Reverse's ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 2938 ms, elapsed time = 3250 ms.

==================== CharIndex ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 1046 ms, elapsed time = 1069 ms.

==================== 5 CharIndex's ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 3266 ms, elapsed time = 3609 ms.

==================== LEN() ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 766 ms, elapsed time = 795 ms.

[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 11

Did that answer your questions, Karthik?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 12

Jeff,

Yes.

Result on my box is here.

SQL Server Execution Times:

CPU time = 16967 ms, elapsed time = 0 ms.

==================== No Mod's (Baseline) ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 438 ms, elapsed time = 482 ms.

==================== SUBSTRING(1,15) ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 797 ms, elapsed time = 798 ms.

==================== LEFT(15) ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 781 ms, elapsed time = 860 ms.

==================== Reverse ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 1181 ms, elapsed time = 1181 ms.

==================== 4 Reverse's ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 2875 ms, elapsed time = 2938 ms.

==================== CharIndex ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 782 ms, elapsed time = 783 ms.

==================== 5 CharIndex's ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 2156 ms, elapsed time = 2163 ms.

==================== LEN() ====================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 464 ms, elapsed time = 464 ms.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 13

Jeff Moden (3/11/2009)
Did that answer your questions, Karthik?

Yes. I got it.Thanks a lot.

So shall we order the string function based on their performance as below?

The order is like fastest,faster,fast,good,normal.

1) LEN()

2) CHARINDEX()

3) SUBSTRING()

4) LEFT()

5) REVERSE()

Can you explain it how it is internally trated by sqlserver? how it is opearting internally? how the memory structure will differ for each function?

if you see REVERSE() is taking more time than other functions. what is happening internally? why it is taking more time?

karthik

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 14

karthikeyan (3/11/2009)
Can you explain it how it is internally trated by sqlserver? how it is opearting internally? how the memory structure will differ for each function?
if you see REVERSE() is taking more time than other functions. what is happening internally? why it is taking more time?

Heh... I leave the answers to those questions up to the Software Engineers of the world. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)