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.
| Author |
Topic |
|
rheitzman
Starting Member
16 Posts |
 Posted - 2005-09-21 : 19:41:37
|
| This should be an easy one but I can't figure it out...SQL Server 2000, DAO and ADO.NetI want to return today's date in a Select statement(not via a stored procedure):select convert(datetime,getdate(),101), --doesn't convert cast(getdate() as varchar(11)), -- returns something useful, but... .... from Areturns:2005-09-21 16:25:35.887 Sep 21 2005 ...The convert() doesn't do anything and always returns the unformatted date. How can I get Convert to work with GetDate()? Or shouldn't I be using GetDate()?The Cast(GetDate() as VarChar(11)) is about as close as I got but that is the format I want. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-21 : 19:55:59
|
convert(varchar(10),getdate(),101)Go with the flow & have fun! Else fight the flow  |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-22 : 01:40:01
|
| I think you need something like thisSelect columns from yourTable where DateDiff(day,DateCol,Getdate())=0MadhivananFailing to plan is Planning to fail |
|
|
|
rheitzman
Starting Member
16 Posts |
Posted - 2005-09-22 : 11:08:46
|
| convert(varchar(10),getdate(),101) is the winner!!! So far at least - any other bright ideas?Poratability Assumption: GetDate() returns the same string regardless of local date settings. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-22 : 11:16:02
|
| getdate returns the time set on the server.Go with the flow & have fun! Else fight the flow |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-23 : 00:59:00
|
| >>GetDate() returns the same string regardless of local date settings.What difficulty do you face with the one that GetDate() returns?MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-23 : 01:28:20
|
| "Poratability Assumption: GetDate() returns the same string regardless of local date settings"The third parameter in the CONVERT function will take care of producing repeatable results for you.However, if you do convert(varchar(10),getdate(),101) you then have the date as a string in a sepcific format, and converting it BACK to a DATETIME might be an issue depending on LOCALE and SET DATEFORMAT settings (but as you are only outputting it in a SELECT resultset I guess that should be fine).Personally I'd format it in the application [i.e. presentation layer]Kristen |
|
|
|
rheitzman
Starting Member
16 Posts |
Posted - 2005-09-23 : 12:20:54
|
| Thanks, all good points!My concern about portability wasn't for me but others who might read this that have code that runs in different localities. I'm guessing GetDate() always returns the same date format regardless of local settings and the varchar(10) param will work in all locations - both the length and the format of the date that is acceptable to CONVERT.RE: Presentation LayerIn my case I'm use dynamic SQL to format the data for display in a grid/report. Specifically I am returned a field that is the date the query was run so the rows are tagged with a easily readable date stamp. I know dynamic SQL doesn't scale well, but it works for for my applications. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-23 : 14:32:03
|
| "I'm guessing GetDate() always returns the same date format"Well ... it returns it as a "date time" datatype - so if, for example, it travels through ADO it will arrive at the other end with that datatype information, so the thing receiving it will know that it is a date/time - doens't mean that the recipient will display it with the correct format! but its better than a 6 digit text string for representing a date (which would be ambiguous, obviously)"the varchar(10) param will work in all locations"The varchar(10) is just "jamming" the data into a container of 10 characters - this has the effect of clipping the data so only the first 10 characters are retained.Its actually the third parameter:convert(varchar(10),getdate(),101)which is controlling the formatting, during the conversion from DateTime datatype to String. And this is guaranteed to happen consistently, regardless of locale.In fact pretty much your only worry is when you convert from a string to a datetime datatype - because a date string is generally ambiguous. So best to use DateTime datatypes at all times, where possible."Specifically I am returned a field that is the date the query was run so the rows are tagged with a easily readable date stamp"That's fine, and I can see why you are doing that.What I was meaning was that the "application" that is receiving the data, including your "Current Time" value, should be responsible for formatting it.Our application, for example, has some logic to display data from the database. Confronted with a DateTime datatype, in a resultset, it would say "Here's a DateTime object, I will display it in dd-Mmm-yyyy format" - that's its default behaviour [designed to be unambiguous, if not necessarily everyones favourite format!]; our application also has "hints" on top of that so that we can indicate that the data should be displayed differently - e.g. to include the Time portion when we need to.Sorry, Rabbiting on, but that's what I was referring to about having the Presentation layer do the formatting; if you do a convert to varchar(10) you have converted the date to text; if something wants to process that date, as a date, it has to be pretty sure that it knows what format it is formatted to - for now, and for ever in the future. Whereas if you send a date to the application as a DateTime datatype there is no ambiguity - but then you will have to format it in the application!This is all pretty irrelevant for your current usage - you just want to get the Current Server Time on your report. But I just thought I would rabbit on in case the distinction is useful to you downstream.I'll go an top up my wine glass now!Kristen |
|
|
|
|
|
|
|
|
Today's Date in Select - GetDate()?