CYYDDD Conversion Problem - 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

CYYDDD Conversion Problem

Author

Topic

dotsofcolor
Starting Member

12 Posts

Posted - 2013-04-21 : 15:48:30

Hello,

I have the following line of code:

DATE(CHAR(CNCL_DTE+1900000)) AS Cancelled_Date

The above changes a date format CYYDDD that comes from a DB2 source to MM\DD\YYYY, which is what I want.

However I have another date from the same source format as above same format CYYDDD, in a different column. I'm using the following code to have it convert the date and omit the zero values (rather than the zeros displaying down the column when a date is not present) from the report that I have populating in SQL Server Reporting Services.

CASE WHEN DATE_SNT = 0 THEN null
ELSE DATE(CHAR(DATE_SNT+1900000))
END AS Last_Sub_Date

Unfortunately it does not convert it as I would expect. I'm getting a format that looks like this: 2013-03-29. I need it to show as 3/29/2013 in the SQL Server Reporting Services report.

I have tried various CAST and Formating options but have yet to get it to display as 3/29/2013.

Any help would be appreciated. Thanks!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-21 : 16:36:57

Try this:
[CODE]
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 101);
[/CODE]

dotsofcolor
Starting Member

12 Posts

Posted - 2013-04-21 : 17:13:55

quote:
Originally posted by MuMu88

Try this:
[CODE]
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 101);
[/CODE]

Thanks for the response MuMu88. I would not be able to check until later but I'm curious though... I would use the CONVERT function after passing it through the CASE WHEN statement? Would I still require the existing statement to remove the zeros that will populate in the field when no date is inserted by the system?

Hope that made sense... thx

dotsofcolor
Starting Member

12 Posts

Posted - 2013-04-21 : 17:46:54

Also I should mention this too. When running the query through TOAD 3.1 the date converts as expected using my original statement, I get MM\DD\YYYY However, when I pass the query through SQL Server reporting services I get the other date format. YYYY-MM-DD.

So I am confused as to why TOAD sees the date in the format that I wanted but once it goes into SSRS it converts the date in the wrong format???

Is there something that I should be doing in SSRS to retain the format?

Sorry, I probably should have included that part in the original post.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-21 : 20:02:59

Here is an example query to convert date style:
[CODE]
select CONVERT(CHAR(8), (Case when Month(getdate()) = 1 then NULL Else DATEADD(Month, 2, getdate()) end), 101) as NewDate;
[/CODE]

I believe the date/time formatting is part of your database default collation setting, which you have to specify at the datbase creation time.

IMHO it is safer to explicitly format your output.

You may want to consult the following sites for more information pertaining to date time formating and styles:
http://msdn.microsoft.com/en-us/library/58c4e64b-5634-4c29-aa22-33193282dd27
http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx
http://msdn.microsoft.com/en-us/library/ms187928.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-22 : 04:33:13

quote:
Originally posted by MuMu88

Here is an example query to convert date style:
[CODE]
select CONVERT(CHAR(8), (Case when Month(getdate()) = 1 then NULL Else DATEADD(Month, 2, getdate()) end), 101) as NewDate;
[/CODE]

I believe the date/time formatting is part of your database default collation setting, which you have to specify at the datbase creation time.

IMHO it is safer to explicitly format your output.

You may want to consult the following sites for more information pertaining to date time formating and styles:
http://msdn.microsoft.com/en-us/library/58c4e64b-5634-4c29-aa22-33193282dd27
http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx
http://msdn.microsoft.com/en-us/library/ms187928.aspx

Not date time formatting but the interpretation of datetime values passed depends on your language and regional settings

see

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

as far as display format goes, i would always prefer to do it at front end otherwise you will have to do lots of explicit cast/converts and also cant use converted values anymore for any date manipulations.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dotsofcolor
Starting Member

12 Posts

Posted - 2013-04-22 : 19:20:12

Thank you to the both of you for the information and code. I look forward to trying this out tomorrow when I pick that project back up again. Also the links were extremely helpful. As always I really appreciate the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-23 : 12:39:36

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources