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

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 & 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.

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 < 10 THEN '0' + CAST ( @_month AS VARCHAR(1)) 
                ELSE CAST ( @_month AS VARCHAR(2)) 
            END 
            + 
            '01' AS DATETIME )
    END

 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"

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)

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.

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'

You need to enclose the date time value in quotes:

DECLARE @Test AS DATETIME 

SET @Test = '2011-02-15'

PRINT @Test

You Should Try This Way :

  DECLARE @TEST DATE
  SET @TEST =  '05/09/2013'
  PRINT @TEST

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'

Try using Select instead of Print

DECLARE @Test AS DATETIME 

SET @Test = '2011-02-15'

Select @Test

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