SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CYYDDD Conversion Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dotsofcolor
Starting Member

12 Posts

Posted - 04/21/2013 :  15:48:30  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/21/2013 :  16:36:57  Show Profile  Reply with Quote
Try this:

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 101);
Go to Top of Page

dotsofcolor
Starting Member

12 Posts

Posted - 04/21/2013 :  17:13:55  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Try this:

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 101);




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
Go to Top of Page

dotsofcolor
Starting Member

12 Posts

Posted - 04/21/2013 :  17:46:54  Show Profile  Reply with Quote
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.

Edited by - dotsofcolor on 04/21/2013 17:58:28
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/21/2013 :  20:02:59  Show Profile  Reply with Quote
Here is an example query to convert date style:

select CONVERT(CHAR(8), (Case when Month(getdate()) = 1 then NULL Else DATEADD(Month, 2, getdate()) end), 101) as NewDate;


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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/22/2013 :  04:33:13  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Here is an example query to convert date style:

select CONVERT(CHAR(8), (Case when Month(getdate()) = 1 then NULL Else DATEADD(Month, 2, getdate()) end), 101) as NewDate;


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
Go to Top of Page

dotsofcolor
Starting Member

12 Posts

Posted - 04/22/2013 :  19:20:12  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/23/2013 :  12:39:36  Show Profile  Reply with Quote
Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000