Convert a date - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert a date

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-25 : 10:19:18

I have successfully converted datetime fields that hold the date and time to show just the date using CONVERT like this

CONVERT(DateTime,MyDateField,103)

However, in another table just the date is stored in the SQL table the sp below displays the date with time shown as 00:00:00

I am unable to display just the date, even if I use CONVERT on it as above.

CREATE Procedure [spRMU_CountRequests]

AS

SELECT DateRequested AS Date_Requested, COUNT(*) AS No_Requests
FROM tblFileRequests
WHERE DateRequested >= '01/01/07'
GROUP BY DateRequested
ORDER BY DateRequested DESC
GO

Kristen
Test

22859 Posts

Posted - 2007-01-25 : 10:22:57

The mantra on this is:

Store your Dates in SQL using a DATETIME datatype.

Format the dates in the client application.

Reason for this is so that the data is transferred to the Client App in a "Date" datatype, allowing the client application to interpret it as such (whereas with a string "date" the client application will be converting it back to Date datatype, with all the usual ambiguity involved, and so on.

Also for any string dates in your SQL use "yyyymmdd" format - so:

WHERE DateRequested >= '20070101'

as SQL Server will treat these unambiguously in all locale settings.

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-25 : 10:23:36

[code]CREATE Procedure [spRMU_CountRequests]

AS

Select convert(varchar(10), DateRequested, 103) as Date_Requested, COUNT(*) AS No_Requests
FROM tblFileRequests
WHERE DateRequested >= '20060101'
GROUP BY DateRequested
ORDER BY DateRequested DESC
GO[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-25 : 10:26:09

Thank you harsh athalye - you have helped me many times and this works again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-25 : 17:00:30

Are you sure the result is what you want ? Your DateRequested contain date & time and you are grouping by DateRequested ?


SELECT dateadd(day, datediff(day, 0, DateRequested), 0) as Date_Requested, COUNT(*) AS No_Requests
FROM  tblFileRequests
WHERE DateRequested >= '20060101'
GROUP BY dateadd(day, datediff(day, 0, DateRequested), 0)
ORDER BY Date_Requested DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources