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
 Site Related Forums
 Article Discussion
 Article: Dates in SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/31/2000 :  20:57:54  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 Posts

Posted - 12/09/2002 :  08:07:37  Show Profile  Reply with Quote
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

United Arab Emirates
1456 Posts

Posted - 12/09/2002 :  08:26:01  Show Profile  Visit mohdowais's Homepage  Reply with Quote
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

Australia
1479 Posts

Posted - 12/09/2002 :  17:50:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 12/09/2002 :  18:05:22  Show Profile  Reply with Quote
use iso standard for dates
yyyymmdd and sql server will be happy.



Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 12/09/2002 :  20:23:29  Show Profile  Reply with Quote
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 - 02/23/2005 :  11:02:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 02/24/2005 :  10:30:23  Show Profile  Reply with Quote
why the love affair with duplicates????

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46287
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.2 seconds. Powered By: Snitz Forums 2000