Applies to:

  • MS SQL Server 2000
  • MS SQL Server 2005

How to enter ANSI dates with MSSQL

  • The data entry depends on the language/localization.
  • To find out the language use the DBCC USEROPTIONS
DBCC USEROPTIONS
go
Set Option                                                                                                                       Value
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------
textsize                                                                                                                         2147483647
rowcount                                                                                                                         10
language                                                                                                                         us_english
dateformat                                                                                                                       mdy
datefirst                                                                                                                        7
lock_timeout                                                                                                                     -1
quoted_identifier                                                                                                                SET
arithabort                                                                                                                       SET
ansi_null_dflt_on                                                                                                                SET
ansi_warnings                                                                                                                    SET
ansi_padding                                                                                                                     SET
ansi_nulls                                                                                                                       SET
concat_null_yields_null                                                                                                          SET
isolation level                                                                                                                  read committed

(14 row(s) affected)

The ones to notice is:

  1. language: us_english
  2. date format: mdy
  3. datefirst: 7 = Sunday

Entering dates

SELECT CURRENT_TIMESTAMP AS 'Right now',
       { d '2007-08-01' } AS 'Date only',
       { ts '2007-08-01 15:00:00' } AS 'Date & time',
       { t '19:21:33' } AS 'Time only'
go
Right now               Date only               Date & time             Time only
----------------------- ----------------------- ----------------------- -----------------------
2007-08-05 17:37:21.687 2007-08-01 00:00:00.000 2007-08-01 15:00:00.000 2007-08-05 19:21:33.000

(1 row(s) affected)
  1. Better use current_timestamp instead of getdate(). CURRENT_TIMESTAMP is ANSI compliant.
  2. { d '2007-08-01' } is the ANSI way of entering the date: the { and using d. It still produces a DATETIME.
  3. { ts '2007-08-01 15:00:00' } is the ANSI way of entering both the date and the time: the { and using ts. The time is entered in the 24 hours format.
  4. { t '19:21:33' } is the ANSI way of entering only the time: the { and using t. The time is entered in the 24 hours format.
    Very important: Although entering the time only, it applies today's date.