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

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

Go to Top of Page

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

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.



Go to Top of Page

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

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

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

- Advertisement -