Using cdate function passing char based date

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)

Using cdate function passing char based date

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-08-29 : 08:16:37

I want to pass 01011900 + 6, can i do cdate('01011900') + 6 in the below statement.

(Case when m_flag = 0 then dateadd (day, numberofdays - 1, Startdate) else '01011900'+6 end )

Thank you very much for the helpful info.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-08-29 : 08:22:19

cdate is not function in SQL Server 2008 see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133912

djj

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-29 : 17:24:52

[code]SELECT CONVERT(DATE,STUFF(STUFF('01011900',5,0,'-'),3,0,'-'))[/code]

Gets you a date with value 1900-01-01

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-29 : 18:01:21

quote:
Originally posted by lazerath

SELECT CONVERT(DATE,STUFF(STUFF('01011900',5,0,'-'),3,0,'-'))

Gets you a date with value 1900-01-01

Not always. It depends if you are using MDY or DMY.
How should SQL Server know how to interpret 06082013?
To be really sure, specify the format code in the CONVERT function.

Using current locale settings (which may be different for many users)

SELECT CONVERT(DATE, STUFF(STUFF('06082013', 5, 0, '-'), 3, 0, '-'))

Force DMY

SELECT CONVERT(DATE, STUFF(STUFF('06082013', 5, 0, '-'), 3, 0, '-'), 105)

Force MDY

SELECT CONVERT(DATE, STUFF(STUFF('06082013', 5, 0, '-'), 3, 0, '-'), 110)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-29 : 18:09:32

Good point SwePeso, thanks for the clarification.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-30 : 00:33:03

isnt this enough?


Case when m_flag = 0 then dateadd (day, numberofdays - 1, Startdate) else 6 end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-30 : 14:58:25

quote:
Originally posted by visakh16

isnt this enough?

Case when m_flag = 0 then dateadd (day, numberofdays - 1, Startdate) else 6 end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Would it actually be "else 7", since he's adding six days to day 1, so the result would be day 7?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-31 : 05:08:13

quote:
Originally posted by ScottPletcher

quote:
Originally posted by visakh16

isnt this enough?

Case when m_flag = 0 then dateadd (day, numberofdays - 1, Startdate) else 6 end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Would it actually be "else 7", since he's adding six days to day 1, so the result would be day 7?!

yep thats true

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources