Best way to convert date format

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

Best way to convert date format

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 16:39:15

The cloumn is BATCH_START_TYPE and type is INT.

It sores the value in MMDDYYYY format. Like 13122009, 2132009 or 2042009. (Length can be 7 or 8 depending on the month between 1-9 or 10-12).

I need to convert this date format to a more sensible YYYYMMDD format (character field).

I've used this.
SELECT
CASE LEN(CONVERT( VARCHAR,BATCH_START_DATE))
WHEN 7 THEN RIGHT(CONVERT( VARCHAR,BATCH_START_DATE),4) + SUBSTRING(CONVERT( VARCHAR,BATCH_START_DATE),2,2) + '0' + SUBSTRING(CONVERT( VARCHAR,BATCH_START_DATE),1,1)
ELSE
RIGHT(CONVERT( VARCHAR,BATCH_START_DATE),4) + SUBSTRING(CONVERT( VARCHAR,BATCH_START_DATE),3,2) + SUBSTRING(CONVERT( VARCHAR,BATCH_START_DATE),1,2)
END AS FROM_DATE
FROM PBDRV

I know this sucks, but my mind has stopped working. Can someone pleas e help me with a better one.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-19 : 16:42:21

Just use CONVERT function with the appropriate style.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-19 : 17:02:40

If it is an INT column then a straight convert won't work. Your samples appear to be ddmmyyyy not "MMDDYYYY".
If the month doesn't have a leading "0" then how can you tell what date this is? 3112009

01/31/2009 OR 11/03/2009 ?

Be One with the Optimizer
TG

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 17:05:04

Thats the problem..the month doesn't have a leading zero.

02/13/2009 gets stored as 2132009 and 12/13/2009 gets stored as 12132009.

the date has a leading zero though for ex: 02/02/2009 gets stored as
2022009.

And it is an INT data type, so I cant use CONVERT directly

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-19 : 17:11:33

Ok let me turn my question around. How are these two dates represented in your system?
01/31/2009
11/03/2009

Be One with the Optimizer
TG

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 17:18:10

01/31/2009 will be 1312009 and 11/03/2009 will be 11032009

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 17:25:01

I changed it a li'l bit to this .

DECLARE @TO_DATE CHAR(8)
SELECT @TO_DATE = RIGHT(REPLICATE('0',8) + CONVERT(VARCHAR,BATCH_SINGLE_DATE),8) FROM PBDRV WHERE DESCRIPTION = 'NIGHTTIME-3' AND SEQUENCE_NUMBER = 1
SELECT @TO_DATE = RIGHT(@TO_DATE,4) + SUBSTRING(@TO_DATE,1,2) + SUBSTRING(@TO_DATE,3,2)
SELECT @TO_DATE = CASE WHEN @TO_DATE > CONVERT(VARCHAR(8),GETDATE(),112) THEN @TO_DATE ELSE CONVERT(VARCHAR(8),GETDATE(),112) END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-19 : 17:36:44

quote:
Originally posted by vijayisonly

01/31/2009 will be 1312009 and 11/03/2009 will be 11032009

But you said the month didn't contain the leading zero - and you supplied examples showing that ?

At any rate there will be no clean way to do this. You'll need to convert the int to string and then manipulate the components. There are countless ways to do that but none significantly better then what you've got.

The way to go is to store dates in a DATETIME or SMALLDATETIME datatype.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-19 : 17:50:06

woops - sorry I confused myself. I see now that your examples can be consistently interpereted as mmddyyyy. So we can count on ddyyyy to always be the last 6 digits. No ambiguity as I originally thought.

But my comment above still stands about storing the value in the correctly typed column. There may be some slick math we can do to derive the correct date - let me think about it...

Be One with the Optimizer
TG

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 19:24:56

Thanks TG..I'm glad I was able to point my point across.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 23:21:43

[code]select (val%10000)as yy,((val/10000)/100)%100 as mm,(val/10000)%100 as dd,dateadd(dd,(val/10000)%100-1,dateadd(mm,((val/10000)/100)%100-1,dateadd(yy,(val%10000) -1900,0)))
FROM
(
SELECT 12132009 as val union all
SELECT 2022009 union all
SELECT 1312009 union all
SELECT 1132009 union all
SELECT 2132009 union all
SELECT 2042009 union all
SELECT 12312007
)t

output
--------------------------------------------
yy mm dd dateval
2009 12 13 2009-12-13 00:00:00.000
2009 2 2 2009-02-02 00:00:00.000
2009 1 31 2009-01-31 00:00:00.000
2009 1 13 2009-01-13 00:00:00.000
2009 2 13 2009-02-13 00:00:00.000
2009 2 4 2009-02-04 00:00:00.000
2007 12 31 2007-12-31 00:00:00.000[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-20 : 03:25:44

Will this be of any use?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources