Date format changes - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Date format changes

Author

Topic

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-15 : 23:47:21

Hi

I have been working on this query and I need some help. This is regarding the date formats.
I have some 3k rows with data as 'm_na_09_22_2011', 'm_emea_30_06_2011',etc.
If you notice the dates, the first entry with na_ (meaning North America) has the date format of mm/dd/yy and the 2nd entry with emea_(meaning Europe,MiddleEast,Africa) has the date format of dd/mm/yy.
I separated the date part using substring into a column. But my problem is I have to take a difference of days between some other column start_date (whichi is in mm/dd/yy format) and this new date column.
How can I convert every date in the new column as mm/dd/yy? in order to get the right difference?
Reminder Note: I am passing a string into the new column as '09/222/2011'
I tried convert and cast but no luck!

I appreciate any help/pointers regarding this. Thanks in advance.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-16 : 00:25:09

Ok, I'm going to preface this with...THIS IS UGLY!!! But I have a lot of experience having to hack through a crappy application with limited SQL Server access (first few years, only had a SQL Lite type of query writer that prohibited variables, DDL etc etc and also has dates in an 8 digit int format (yyyymmdd) so I've become quite adept and kludging through queries to get what I need. At first, I tried to just convert the european date to "DATE" but it didn't like the dd-mm-yyyy format, so I wrapped that column (variable here) in a substring.

I'm hoping someone has something cleaner, but this works:

declare @na varchar(20)
declare @m_emea varchar(20)
set @na='m_na_09_22_2011'
set @m_emea='m_emea_30_06_2011'

select --Extract Date from string for NA Date
CAST(REPLACE(REPLACE(@na,'m_na_',''),'_','/') AS DATE) AS 'Converted M_NA Date',
--Extract Date from string for EMEA Date
CONVERT(DATE,
RIGHT(REPLACE(REPLACE(@m_emea,'m_emea_',''),'_','-'),4)+'-'+
SUBSTRING(REPLACE(REPLACE(@m_emea,'m_emea_',''),'_','-'),4,2)+'-'+
SUBSTRING(REPLACE(REPLACE(@m_emea,'m_emea_',''),'_','-'),1,2)) AS 'Converted M_EMEA DATE',
--Begin DateDiff using two converted dates
DATEDIFF(DD,
CAST(REPLACE(REPLACE(@na,'m_na_',''),'_','/') AS DATE),

CONVERT(DATE,
RIGHT(REPLACE(REPLACE(@m_emea,'m_emea_',''),'_','-'),4)+'-'+
SUBSTRING(REPLACE(REPLACE(@m_emea,'m_emea_',''),'_','-'),4,2)+'-'+
SUBSTRING(REPLACE(REPLACE(@m_emea,'m_emea_',''),'_','-'),1,2))) AS 'DATE DIFF SAMPLE'

This should be cleaner...still icky, but cleaner:

[CODE]
declare @na varchar(20)
declare @m_emea varchar(20)
set @na='m_na_09_22_2011'
set @m_emea='m_emea_30_06_2011'

SELECT --Extract Date from string for NA Date
CAST(REPLACE(REPLACE(@na,'m_na_',''),'_','/') AS DATE) AS 'Converted M_NA Date',
--Extract Date from string for EMEA Date
CONVERT(DATE, REPLACE(REPLACE(@m_emea,'m_emea_',''),'_','-'),105) AS 'Converted M_EMEA DATE',
--Begin DateDiff using two converted dates
DATEDIFF(DD,CAST(REPLACE(REPLACE(@na,'m_na_',''),'_','/') AS DATE),
CONVERT(DATE, REPLACE(REPLACE(@m_emea,'m_emea_',''),'_','-'),105)) AS 'DATE DIFF SAMPLE'[/CODE]

EDIT: This also assumes that you will consistently be able to predict the formats of the data.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-16 : 09:17:00

SS, assuming you have your M_NA and E_EMEA dates on the same row (ie, they're not all found in one column) you could also handle the conversion in a CTE so that it would be easier to deal with for all your dateadd/datediff functions:

[CODE]
DECLARE @DatesToFormat TABLE (NA_Date varchar(20), E_EMEA_Date varchar(20))
INSERT INTO @DatesToFormat (NA_Date, E_EMEA_Date)
VALUES ('m_na_09_22_2011','m_emea_30_06_2011')

;WITH ConvertedDates
AS (
SELECT CAST(REPLACE(REPLACE(NA_Date,'m_na_',''),'_','/') AS DATE) AS NA_DATE,
CONVERT(DATE, REPLACE(REPLACE(E_EMEA_Date,'m_emea_',''),'_','-'),105) AS E_MEA_DATE
FROM @DatesToFormat)

SELECT NA_DATE, E_MEA_DATE, DATEDIFF(dd, NA_DATE, E_MEA_DATE)
FROM ConvertedDates[/CODE]

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-16 : 16:00:21

Thanks so much for you reply.
But I want to be more clearer on my problem.
Actually I cut the date part from the text and put it in the separate column.
Keeping the big picture apart, please try to answer this question:
If I have a column with 100 rows populated with dates passed as string, in American format (mm/dd/yy) as: '06/30/08','05/05/08','04/28/08','04/07/08', and so on....
and if we want to convert them to European format (dd/mm/yy), How can we accomplish that?
Remember we are just passing a string.
I tried to use convert(varchar,columnname,105) but still shows the same as before, no change. Ideas??

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-16 : 16:22:00

Is it always mm/dd/yy?

If so, then:


Declare @t table (
	dtColumn varchar(10)
)

Insert Into @t Select '06/30/08'
Insert Into @t Select '05/05/08'
Insert Into @t Select '04/28/08'
Insert Into @t Select '04/07/08'

Select 
	dtColumn,
	stuff(right(dtColumn,5),4,0,left(dtColumn,3))
From @t

Corey

I Has Returned!!

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-16 : 16:28:16

I didn't understand what it does exactly, But Awesome!!!! it worked like a piece of cake. I have been pulling my hair on this for 3 days.

Thanks a ton! If you have time can you explain it in detail, I will try to search for it too.

Thanks so much!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-16 : 16:35:48

right(stringVal, N) and left(stringVal, N) are just string functions that return the right N or left N characters of stringVal.

Stuff(stringVal, startPosition, N, stringVal2) is a string function that stuffs stringVal2 into stringVal starting at postion startPosition and overwriting up to N characters...

So, essentially:


         = 06/30/08
Left(3)  = 06/
Right(3) = 30/08

Stuff()  = 30/|06/|08

         = 30/06/08

Thats all there is to it

Corey

I Has Returned!!

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-16 : 16:47:33

I didn't know about this function before. Thank you so much for explaining it. It makes sense! Its exactly what I wanted.

harry123
Starting Member

13 Posts

Posted - 2011-08-16 : 17:22:04

SELECT CONVERT(VARCHAR,DATECOLUMN,4)

FORMAT: DD.MM.YY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources