| Author |
Topic |
|
Steve2106
Posting Yak Master
183 Posts |
 Posted - 2013-09-09 : 05:08:51
|
| 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 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-09-09 : 09:26:44
|
| If you want to change it to a date, you can do that directly (on SQL 2008 or higher):CONVERT(date, LiveData.DateRaised) |
 |
|
|
Cooper-5
Starting Member
10 Posts |
Posted - 2013-09-09 : 09:29:49
|
| 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 formatas 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 helpsNothing is truly idiot proof, because the world keeps producing a higher class of idiot |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-09-09 : 09:33:24
|
| 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. |
|
|
|
Cooper-5
Starting Member
10 Posts |
Posted - 2013-09-09 : 09:39:04
|
| 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 |
|
|
|
Steve2106
Posting Yak Master
183 Posts |
Posted - 2013-09-09 : 10:09:54
|
| 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 |
|
|
|
Cooper-5
Starting Member
10 Posts |
Posted - 2013-09-09 : 10:13:21
|
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. |
|
|
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2013-09-10 : 01:50:03
|
| convert(char(11), LiveData.DateRaised, 113)AS "Date Raised"veeranjaneyulu |
|
|
|
Steve2106
Posting Yak Master
183 Posts |
Posted - 2013-09-10 : 04:03:48
|
| Hi Everyone,Thanks for all your help, I have got this working nicely now.I appreciate your time.Best Regards,Steve |
|
|
|
|
Remove time from datetime