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 2000 Forums
 Transact-SQL (2000)
 Today's Date in Select - GetDate()?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rheitzman
Starting Member

16 Posts

Posted - 09/21/2005 :  19:41:37  Show Profile  Reply with Quote
This should be an easy one but I can't figure it out...

SQL Server 2000, DAO and ADO.Net

I 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 A

returns:
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

Slovenia
11750 Posts

Posted - 09/21/2005 :  19:55:59  Show Profile  Visit spirit1's Homepage  Reply with Quote
convert(varchar(10),getdate(),101)

Go with the flow & have fun! Else fight the flow
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/22/2005 :  01:40:01  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I think you need something like this

Select columns from yourTable where DateDiff(day,DateCol,Getdate())=0

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rheitzman
Starting Member

16 Posts

Posted - 09/22/2005 :  11:08:46  Show Profile  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 09/22/2005 :  11:16:02  Show Profile  Visit spirit1's Homepage  Reply with Quote
getdate returns the time set on the server.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/23/2005 :  00:59:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>GetDate() returns the same string regardless of local date settings.

What difficulty do you face with the one that GetDate() returns?


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/23/2005 :  01:28:20  Show Profile  Reply with Quote
"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
Go to Top of Page

rheitzman
Starting Member

16 Posts

Posted - 09/23/2005 :  12:20:54  Show Profile  Reply with Quote
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 Layer

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

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/23/2005 :  14:32:03  Show Profile  Reply with Quote
"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
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.08 seconds. Powered By: Snitz Forums 2000