CODEWITHSUNDEEP
sql-server
Sohel Ahmed
Sohel Ahmed

Reputation: 169

CONVERT function in SQL Server with int datatype for Date functions

I've tried to print to print today's date in VARCHAR format it prints well but when I tried to print the same date in int it shows 43004. I know that we can't print date in integer format but what is the significance of that number 43004.

SELECT CONVERT(INT, GETDATE(), 110);

Output: 43004

SELECT CONVERT(VARCHAR, GETDATE(), 110);

Output: 09-27-2017

Thanks in advance.

Upvotes: 1

Views: 257

Answers (2)

S3S
S3S

Reputation: 25112

As stated in the comments, it's the number days since 1901-01-01.

You can see this by running:

select dateadd(day,43004,'19000101')

Another way to get the same result, is:

select datediff(day,getdate(),0)

In the query above, 0 is '1900-01-01' represented as an INT. You'll commonly see similar methods for finding the first day of the current month.

select dateadd(month, datediff(month, 0, getdate()), 0) AS StartOfMonth

Upvotes: 2

DhruvJoshi
DhruvJoshi

Reputation: 17126

You should try to see the output of the query

select cast(0 as datetime)
, getdate(),
cast(getdate() as int)

output

01.01.1900 00:00:00     27.09.2017 16:09:25    43004

So 0 is the value of 1.1.1900 which is the lowest value of date time allowed and every single day after it gets a number after 0 so 1 is 02.01.1900 00:00:00

Upvotes: 1

Related Questions