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.
You need to enclose the date time value in quotes:
DECLARE @Test AS DATETIME
SET @Test = '2011-02-15'
PRINT @Test
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)
DATETIME datatype without errors. DATETIME is extremely picky (and illogical) that way.....CONVERT(DATETIME, '2011-08-25') will FAIL - whereas CONVERT(DATETIME, '20110825') works - always - regardless of language settingsCONVERT(DATETIME, '2011-08-25', 120) succeeds. You just need to specify the format and then CONVERT will be able to parse an ISO 8601 date directly.CAST which doesn't support formats to be specified. The adapted ISO-8601 format always works - no matter what settings you have. I'll pick that for my usage2011-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'
Try using Select instead of Print
DECLARE @Test AS DATETIME
SET @Test = '2011-02-15'
Select @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'
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
Note that the T in the ISO 8601 is actually the letter T and not a variable.
You Should Try This Way :
DECLARE @TEST DATE
SET @TEST = '05/09/2013'
PRINT @TEST
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"
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
