How to set a DateTime variable in SQL Server 2008

Question

SQL Server 2008 is not doing what I expected with DateTime  It doesn t let me set DateTime variables  no matter what date format I use   When I execute   DECLARE  Test AS DATETIME SET  Test   2011-02-15 PRINT  Test   I get an output of   Jun 18 1905 12 00AM   I ve checked all of the regional settings that I can find  amp  it all appears okay  I ve also tried setting the DateTime to various literal alternatives  such as  15 02 2011    2011-02-15 00 00 00   etc

User · Answer

2011-01-15   2011-16   1995   This is then being implicitly converted from an integer to a date  giving you the 1995th day  starting from 1st Jan 1900   You need to use SET  test    2011-02-15

User · Answer

You Should Try This Way      DECLARE  TEST DATE   SET  TEST     05 09 2013    PRINT  TEST

User · Answer

You want to make the format style explicit and don t rely on interpretation based on local settings  which may vary among your clients infrastructure   DECLARE  Test AS DATETIME SET  Test   CONVERT DATETIME   2011-02-15 00 00 00   120  -- yyyy-MM-dd hh mm ss SELECT  Test  While there is a plethora of styles  you may want to remember few  126  ISO 8601   yyyy-MM-ddThh mm ss  mmm  120  yyyy-MM-dd hh mm ss 112  yyyyMMdd  Note that the T in the ISO 8601 is actually the letter T and not a variable

User · Answer

Just to explain   2011-02-15 is being interpreted literally as a mathematical operation  to which the answer is 1994     This  then  is being interpreted as 1994 days since the origin of date  Jan 1st 1900    1994 days   5 years  6 months  18 days   June 18th 1905  So  if you don t want to to the calculation each time you want compare a date to a particular value use the standard  Compare the value of the toString   function of date object to the string like this     set  TEST    2011-02-05

User · Answer

1  I create new Date   and convert her in String    2  This string I set in insert      Example     insert into newDate date create  VALUES              PreparedStatement ps   con prepareStatement CREATE           ps setString 1  getData             ps executeUpdate               private String getData           SimpleDateFormat sdf   new SimpleDateFormat  yyyy-M-dd hh mm ss         return  sdf format new java util Date               It is very important format      yyyy-M-dd hh mm ss

User · Answer

The CONVERT function helps Check this  declare  erro event timestamp as Timestamp  set  erro event timestamp   CONVERT Timestamp    2020-07-06 05 19 44 380    121    The magic number 121 I found here  https   www w3schools com SQL func sqlserver convert asp

User · Answer

Try using Select instead of Print  DECLARE  Test AS DATETIME   SET  Test    2011-02-15   Select  Test

User · Answer

Check This   DECLARE       month TINYINT           5        year SMALLINT           2020       date ref DATETIME        NULL        IF   year IS NULL         SET  date ref   GETDATE   - 430     ELSE     BEGIN         SELECT  date ref   CAST   CAST     year AS VARCHAR  4                               CASE                  WHEN   month  lt  10 THEN  0    CAST     month AS VARCHAR 1                    ELSE CAST     month AS VARCHAR 2                END                              01  AS DATETIME       END

User · Answer

First of all - use single quotes around your date literals   Second of all  I would strongly recommend always using the ISO-8601 date format - this works regardless of what your locale  regional or language settings are on your SQL Server   The ISO-8601 format is either    YYYYMMDD for dates only  e g  20110825 for the 25th of August  2011  YYYY-MM-DDTHH MM SS for dates and time  e g  2011-08-25T14 15 00 for 25th of AUgust  14 15 2 15pm in the afternoon

User · Answer

You need to enclose the date time value in quotes   DECLARE  Test AS DATETIME   SET  Test    2011-02-15   PRINT  Test

[sql] How to set a DateTime variable in SQL Server 2008?

Examples related to sql

Examples related to datetime