DateTime Format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DateTime Format

Author

Topic

TechNess
Starting Member

11 Posts

Posted - 2010-10-27 : 02:07:31

Hi all,
I need a small help. I am trying to convert string of the format '20101022-120100' to the datetime format of 'yyyy-mm-dd hh:mm:ss.mmm'. I used: CONVERT(varchar, CONVERT(datetime, '20100722-000100'), 121) for that and got some error. If I remove "-000100" part it works.

Can anyone tell me how to do it in SQL?

**TechNess**

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-27 : 04:47:44

Well this thing you should do in front end only as generally front end applications have rich functionality for formatting.
but still you if want in SQL.
Try this -
It is very confusing but can solve your problem -

DECLARE @Sample AS VARCHAR(100)
SET @Sample = '20100722-000100'
SELECT CONVERT(DATETIME,
CONVERT(VARCHAR, CONVERT(DATETIME, LEFT(@Sample , CHARINDEX('-',@Sample)-1)), 101)
+ ' ' +
left(left(left(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2)+':'+substring(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2 , len(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)))-1),5)+':'+substring(left(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2)+':'+substring(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2 , len(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)))-1),6 , len(left(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2)+':'+substring(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2 , len(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)))-1))-1),8)+':'+substring(left(left(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2)+':'+substring(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2 , len(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)))-1),5)+':'+substring(left(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2)+':'+substring(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2 , len(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)))-1),6 , len(left(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2)+':'+substring(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2 , len(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)))-1))-1),9 , len(left(left(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2)+':'+substring(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2 , len(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)))-1),5)+':'+substring(left(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2)+':'+substring(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2 , len(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)))-1),6 , len(left(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2)+':'+substring(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)),2 , len(RIGHT(@Sample, LEN(@Sample) - CHARINDEX('-',@Sample)))-1))-1))-1)
,101)

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

edit: to remove code tags so it can be read on a monitor less than 300 feet wide

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-27 : 05:20:40

DECLARE @Sample AS VARCHAR(100)
SET @Sample = '20100722-120100'
select convert(datetime,stuff(stuff(REPLACE(@sample,'-',' '),12,0,':'),15,0,':'),112)

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-27 : 05:47:27

@Madhi - Initialy I was trying to do with stuff but i could not. Thanks

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-27 : 08:39:58

quote:
Originally posted by vaibhavktiwari83

@Madhi - Initialy I was trying to do with stuff but i could not. Thanks

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

No problem

Madhivanan

Failing to plan is Planning to fail

TechNess
Starting Member

11 Posts

Posted - 2010-10-27 : 09:55:44

Thanks, it works fine.

quote:
Originally posted by madhivanan

DECLARE @Sample AS VARCHAR(100)
SET @Sample = '20100722-120100'
select convert(datetime,stuff(stuff(REPLACE(@sample,'-',' '),12,0,':'),15,0,':'),112)

Madhivanan

Failing to plan is Planning to fail

**TechNess**

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-27 : 11:25:42

Sorry for not really adding anything to the conversation, but I was just goofing around with the DATE and TIME datatypes and was beatting my head against the wall until I realized that you can't add DATE and TIME datatypes (duh). Although, it is not any more elegant of a solution, I'll post it anyway:

DECLARE  @MyDate VARCHAR(15) = '20101022-120100' 

SELECT CAST(LEFT(@MyDate, 8) AS DATETIME) + CAST(STUFF(STUFF(RIGHT(@MyDate, 6), 3, 0, ':'), 6, 0, ':') AS TIME)

TechNess
Starting Member

11 Posts

Posted - 2010-10-27 : 12:31:10

Got to modify little bit but it worked. Thanks!
DECLARE @MyDate VARCHAR(15)
Set @MyDate = '20101022-120100'
SELECT CAST(LEFT(@MyDate, 8) AS DATETIME) + CAST(STUFF(STUFF(RIGHT(@MyDate, 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)

quote:
Originally posted by Lamprey

Sorry for not really adding anything to the conversation, but I was just goofing around with the DATE and TIME datatypes and was beatting my head against the wall until I realized that you can't add DATE and TIME datatypes (duh). Although, it is not any more elegant of a solution, I'll post it anyway:
DECLARE @MyDate VARCHAR(15) = '20101022-120100'

SELECT CAST(LEFT(@MyDate, 8) AS DATETIME) + CAST(STUFF(STUFF(RIGHT(@MyDate, 6), 3, 0, ':'), 6, 0, ':') AS TIME)

**TechNess**

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-28 : 02:02:30

quote:
Originally posted by Lamprey

Sorry for not really adding anything to the conversation, but I was just goofing around with the DATE and TIME datatypes and was beatting my head against the wall until I realized that you can't add DATE and TIME datatypes (duh). Although, it is not any more elegant of a solution, I'll post it anyway:
DECLARE @MyDate VARCHAR(15) = '20101022-120100'

SELECT CAST(LEFT(@MyDate, 8) AS DATETIME) + CAST(STUFF(STUFF(RIGHT(@MyDate, 6), 3, 0, ':'), 6, 0, ':') AS TIME)

Provided the version is from 2008 onwards

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources