Changing Datetime to Int and set it to beginning of month

Question

Changing Datetime to Int and set it to beginning of month

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
October 7, 2014 at 4:26 am

#291305

Hi Guys and Girls,
I have the following:
SELECT CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
The first sets getdate() to an int.
The second set the date to the first of the month.
Does anyone have an idea how I can combine the two to set the date to the beginning of the month but still as an integer?
Kind regards
Fred

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

SELECT YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + 1;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 2

frdrckmitchell7

Ten Centuries

Points: 1351

October 7, 2014 at 4:58 am

#1750518

Thanks Koen.

ScottPletcher SSC Guru Points: 101078 More actions · Answer 3

For consistency, I'd stick with just explicitly CASTing the second value to an int:

SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS int)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

ScottPletcher (10/7/2014)
For consistency, I'd stick with just explicitly CASTing the second value to an int:
SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS int)

I agree with you, but you missed the intermediate convert.

SELECT CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0), 112) AS int)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101078 More actions · Answer 5

Luis Cazares (10/7/2014)
ScottPletcher (10/7/2014)
For consistency, I'd stick with just explicitly CASTing the second value to an int:
SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS int)
I agree with you, but you missed the intermediate convert.
SELECT CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0), 112) AS int)

Hmm, but no intermediate conversion is needed. Dates already are ints, and ints are de facto dates.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".