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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 465, Visits: 1,544
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: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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 @ 1:36 AM
Points: 1,950, Visits: 20,197
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: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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