Article: Dates in SQL Server - 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

Site Related Forums

Article Discussion

Article: Dates in SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-07-31 : 20:57:54

Assorted readers have written "Can i get system date in sql stmnts directly thru any function? and I want to take a date from the html form and wish to pass on to the stored procedure where the value is inserted

Article Link.

mbonev
Starting Member

1 Post

Posted - 2002-12-09 : 08:07:37

Be aware that
Insert MyTable(DateField)
Values ( convert (datetime, '3/15/2004'))

works only if SQL Server is configured to use US dates. But SQL Server supports other represantation of the date, not only the glorious US date

Check the "SET DATEFORMAT xxx" sql command and you'll see 3 options to choose from:
dd/mm/yyyy, mm/dd/yyyy & yyyy/mm/dd.
Having this in mind your example will not work if
DATEFORMAT is set to dd/mm/yyyy

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2002-12-09 : 08:26:01

quote:

Check the "SET DATEFORMAT xxx" sql command and you'll see 3 options to choose from:
dd/mm/yyyy, mm/dd/yyyy & yyyy/mm/dd.

er, small correction there: BOL mentions the following options for SET DATEFORMAT - mdy, dmy, ymd, ydm, myd, and dym

e.g SET DATEFORMAT dmy

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-09 : 17:50:24

Gotta say that I always use (even internally in code) the dd mmm yyyy format
ie convert(nvarchar,getdate(),106).

just haven't been able to trip myself up with this yet, as hard as I try

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-09 : 18:05:22

use iso standard for dates
yyyymmdd and sql server will be happy.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-09 : 20:23:29

Use 106 and both the server and the users will be happy

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ralphceo
Starting Member

2 Posts

Posted - 2005-02-23 : 11:02:58

DECLARE @FromDay int, @ToDay int, @FromTime varchar(10), @ToTime varchar(10)
DECLARE @Day int
DECLARE @RangeFrom datetime, @RangeTo datetime

SET @Day = DatePart (dw, getDate())
SET @FromDay = 2
SET @ToDay = 6
SET @FromTime = '06:00'
SET @ToTime = '17:00'

IF (@Day >= @FromDay) AND (@Day <= @ToDay) BEGIN
PRINT 'Day ' + CAST(@Day AS varchar)
PRINT 'FromDay ' + CAST(@FromDay as varchar)
PRINT 'ToDay ' + cast(@ToDay as varchar)
SET @RangeFrom = CONVERT(varchar, GetDate() - (@Day - @FromDay), 110) + ' ' + @FromTime
SET @RangeTo = CONVERT(varchar, GetDate() + (@ToDay - @Day), 110) + ' ' + @ToTime
PRINT @RangeFrom
PRINT GetDate()
PRINT @RangeTo
IF (GetDate() >= @RangeFrom AND GetDate() <= @RangeTo) BEGIN
PRINT 'Between Dates'
END
END

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-02-24 : 10:30:23

why the love affair with duplicates????

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46287

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources