Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

GetDate - in where clause Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 1:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:13 AM
Points: 16, Visits: 27
Hi,

I need to get extract the year and month from the currentdate and concatenate with 01

YYYYMM01

e.g. 20140501

I have tried various ways :

left
datepart
cast
convert

But just not happening, please can anyone help...

Thanks
Post #1568668
Posted Wednesday, May 7, 2014 1:34 PM This worked for the OP Answer marked as solution
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 8:06 AM
Points: 516, Visits: 1,793
SELECT YEAR(GETDATE())*10000 + MONTH(GETDATE())*100 + DAY(GETDATE())
Post #1568673
Posted Wednesday, May 7, 2014 1:38 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 13,969, Visits: 13,560
Here is another way.

select CONVERT(varchar, dateadd(mm, datediff(mm, 0, GETDATE()), 0), 112)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568675
Posted Wednesday, May 7, 2014 1:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:13 AM
Points: 16, Visits: 27
wow thanks for the quick reply it worked, i changed the day to

+ '01' as need it to represent 01 always.

Intrigued as to why need to *1000 for year and *100 month?

Will this still if work if month = 10, 11 or 12?
Post #1568676
Posted Wednesday, May 7, 2014 1:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 13,969, Visits: 13,560
SQL Guy 1 (5/7/2014)
SELECT YEAR(GETDATE())*10000 + MONTH(GETDATE())*100 + DAY(GETDATE())


You could just use convert on GETDATE() to accomplish this.

select CONVERT(varchar, getdate(), 112)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568678
Posted Wednesday, May 7, 2014 1:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:13 AM
Points: 16, Visits: 27
hi sean,

i always need the day to be 01
Post #1568680
Posted Wednesday, May 7, 2014 1:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 1,991, Visits: 20,971
select CONVERT(varchar(6), getdate(), 112) +'01'

______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1568681
Posted Wednesday, May 7, 2014 1:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 13,969, Visits: 13,560
Informer30 (5/7/2014)
hi sean,

i always need the day to be 01


Yeah that is exactly what my first post does.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568682
Posted Wednesday, May 7, 2014 2:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:13 AM
Points: 16, Visits: 27
ok thanks guys all great replies....
Post #1568685
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse