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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Remove time from datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Steve2106
Posting Yak Master

United Kingdom
166 Posts

Posted - 09/09/2013 :  05:08:51  Show Profile  Reply with Quote
Hi There,

In a query I am able to remove the time portion of a datetime field using:
cast(convert(char(11), LiveData.DateRaised, 113) as datetime) AS "Date Raised",

But when I export the results from the query the time portion is still there. Am I missing something.

Thanks for any help you can give.

Best Regards,






Steve

Edited by - Steve2106 on 09/09/2013 05:43:33

ScottPletcher
Constraint Violating Yak Guru

USA
364 Posts

Posted - 09/09/2013 :  09:26:44  Show Profile  Reply with Quote
If you want to change it to a date, you can do that directly (on SQL 2008 or higher):

CONVERT(date, LiveData.DateRaised)
Go to Top of Page

Cooper-5
Starting Member

United Kingdom
10 Posts

Posted - 09/09/2013 :  09:29:49  Show Profile  Reply with Quote
Hi Steve,

Can i inquire as to what you are exporting too, and also why the requirement for casting back to a datetime type after the convert statement?

the convert will give you a text date with no time but the the datetime datatype should always return you a midnight time against any unset time portion of a datetime variable.

if you merely wanted a text format date output, then as part of your export script just replace the field with your SQL convert statement:
convert(char(11), LiveData.DateRaised, 103)
this would give you a date in a dd/mm/yyyy format

as soon as you cast it back to a datetime the time instance is set by default to midnight as far as im aware.

apologies if i misunderstood your question but hope this helps




Nothing is truly idiot proof, because the world keeps producing a higher class of idiot
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
364 Posts

Posted - 09/09/2013 :  09:33:24  Show Profile  Reply with Quote
If you're going to store a date as text, store it as 'YYYYMMDD', which will always be interpreted correctly by SQL (no matter what the date and/or language settings are), and can be sorted directly as well.
Go to Top of Page

Cooper-5
Starting Member

United Kingdom
10 Posts

Posted - 09/09/2013 :  09:39:04  Show Profile  Reply with Quote
i agree with you there scott, ive used that approach a couple of times especially with regards to passing date time parameters back from the oldschool VBA applications to SQL procedures.

it saves so much time messing around with formatting based on locality, but there seems no need for it now with .net applications.

apologies for going slightly off topic on this one steve, hope the converts help you out.

Nothing is truly idiot proof, because the world keeps producing a higher class of idiot
Go to Top of Page

Steve2106
Posting Yak Master

United Kingdom
166 Posts

Posted - 09/09/2013 :  10:09:54  Show Profile  Reply with Quote
Hi There,

Thanks for your replies.

I am exporting to Excel.
The convert I used I got from the internet and now you point it out, obviously if I convert back to a DateTime it will give me a Date with Time. Doh.

Best Regards,



Steve
Go to Top of Page

Cooper-5
Starting Member

United Kingdom
10 Posts

Posted - 09/09/2013 :  10:13:21  Show Profile  Reply with Quote
yeah easy mistake to make though, ive done it on more than one occasion

if you want more flexibility on the outputted date formats have a look at the convert functions 3rd parameter on technet or msdn, there are a substantial number of formats you can output too with that function.

Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
165 Posts

Posted - 09/10/2013 :  01:50:03  Show Profile  Reply with Quote
convert(char(11), LiveData.DateRaised, 113)AS "Date Raised"

veeranjaneyulu
Go to Top of Page

Steve2106
Posting Yak Master

United Kingdom
166 Posts

Posted - 09/10/2013 :  04:03:48  Show Profile  Reply with Quote
Hi Everyone,

Thanks for all your help, I have got this working nicely now.

I appreciate your time.

Best Regards,



Steve
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.09 seconds. Powered By: Snitz Forums 2000