Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Monday, October 12, 2015 9:56 AM
Points: 22, Visits: 36
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 607, Visits: 2,348
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 15,902, Visits: 16,353
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: Monday, October 12, 2015 9:56 AM
Points: 22, Visits: 36
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 15,902, Visits: 16,353
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: Monday, October 12, 2015 9:56 AM
Points: 22, Visits: 36
hi sean,

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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 3,107, Visits: 30,864
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 15,902, Visits: 16,353
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: Monday, October 12, 2015 9:56 AM
Points: 22, Visits: 36
ok thanks guys all great replies....
Post #1568685
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse