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
 Date function Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 03/13/2013 :  05:11:02  Show Profile  Reply with Quote
Hey guys

Sorry to bother you so early in the morning, but i need some help and advice.
This is my table
SELECT TOP 5
[FDMSAccountNo]
,[Open_Date]
FROM [FDMS].[dbo].[Dim_Outlet]

Which produces the following results
FDMSAccountNo Open_Date
878000000884 20030521
878000001882 20090709
878000002880 20090709
878000003888 20090709
878000004886 20090709
I want to change the open date into the following format ā€œ2003-05-21T12:00:00Zā€

Required outcome
FDMSAccountNo Open_Date
878000000884 2003-05-21 T12:00:00Z
878000001882 2009-07-09 T12:00:00Z
878000002880 2009-07-09 T12:00:00Z
878000003888 2009-07-09 T12:00:00Z
878000004886 2009-07-09 T12:00:00Z

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/13/2013 :  06:39:25  Show Profile  Reply with Quote
this should be done at the front end application layer as its a presentation issue. Handling this in sql end will require casting to varchar which is bit of an overkill and will make further date manipulation with these values difficult

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 03/13/2013 :  06:45:14  Show Profile  Reply with Quote
Sorry if this double posts, my posts don't seem to be showing up.

You probably want to output it for xml datetime 126

check the below link for a full list of options. You may need to add a +'z' at the end though.

http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx
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.22 seconds. Powered By: Snitz Forums 2000