How to specify "Current Year" plus May 1st? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to specify "Current Year" plus May 1st?

Author

Topic

MCusuma1
Starting Member

13 Posts

Posted - 2012-08-14 : 10:50:58

I have been trying to write a script that pulls data from a table and places it into a new table with a WHERE statement that indicates [CURRENT YEAR]\05\01. Here is what I have tried as a test to see if I can get the syntax to work in an independent query:

CREATE TABLE DATE(C1 datetime2, C2 varchar)
INSERT INTO DATE(C1)
VALUES (CONVERT (datetime2, CONVERT(varchar, year(getdate())) + "05-01 00:00:00.0000000"))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 10:53:33

INSERT INTO DATE(C1)
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),'19000501')
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 10:57:56

see logic here

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MCusuma1
Starting Member

13 Posts

Posted - 2012-08-14 : 11:09:11

Thanks I'll try that.

MCusuma1
Starting Member

13 Posts

Posted - 2012-08-14 : 11:26:10

Worked like a charm thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 12:48:40

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-08-16 : 10:38:53

Also
SELECT dateadd(month,5-1,dateadd(year,year(getdate())-1900,0))

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-08-16 : 10:40:39

quote:
Originally posted by MCusuma1

I have been trying to write a script that pulls data from a table and places it into a new table with a WHERE statement that indicates [CURRENT YEAR]\05\01. Here is what I have tried as a test to see if I can get the syntax to work in an independent query:

CREATE TABLE DATE(C1 datetime2, C2 varchar(specify size))
INSERT INTO DATE(C1)
VALUES (CONVERT (datetime2, CONVERT(varchar, year(getdate())) + "05-01 00:00:00.0000000"))

See here for reason
http://beyondrelational.com/modules/2/blogs/70/posts/10824/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources