format date - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

format date

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-30 : 14:35:22

i am trying to convert the following to sql

dateserial(2006,12,10)

what's the sql version?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-30 : 15:05:20

select dateadd(year, 2006 - 1900, dateadd(month, 12, dateadd(day, 10, -32)))

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-30 : 15:11:50

so that i understand why aer you ding -1900 and -32?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-30 : 15:16:07

I read about the DATEADD function in Books Online for understanding what the function does and how it works.
Trust me. You need these numbers.
If not, play around with the numbers yourself to get a grip of what is going on.

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-30 : 15:19:09

so no matter what date -- i always use the -1900 adn -32?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-30 : 15:25:40

What did Books Online tell you?
What did you discover when you fiddled around with the numbers and actually tried for yourself?

How do you think the tw numbers {-32, -1900} relate to the default zero date in SQL Server?

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-12-30 : 18:09:06

Well the 32 is not necessary.

simpler?

-- dateserial(2006,12,10)
select cast('20061210' as datetime)

-- dateserial(2006,12,10)
select dateadd(day,10-1,dateadd(month,12-1,dateadd(year,2006-1900,0)))
other way round:
select dateadd(year,2006-1900,dateadd(month,12-1,dateadd(day,10-1,0)))

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-30 : 22:57:30

ooo!

me too:
Select dateadd(day,10-1,dateadd(month,(2006-1900)*12+12-1,0))

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-31 : 01:16:02

i think the select cast will work but is it always in the format of yyyymmdd regardless of the date format for the country the server is on?

Kristen
Test

22859 Posts

Posted - 2006-12-31 : 04:30:15

"is it always in the format of yyyymmdd regardless of the date format for the country the server is on?"

Yes, "string" dates in YYYYMMDD are unambiguous to SQL Server (note that it is important that there is NO punctuation - so, for example, YYYY-MM-DD is NOT a valid alternative in this regard)

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-12-31 : 05:17:08

> "i think the select cast will work but is it always in the format of yyyymmdd regardless of the date format for the country the server is on?"

check, confirm, yes :)

>"ooo!
me too:
Select dateadd(day,10-1,dateadd(month,(2006-1900)*12+12-1,0))"
Long time no see Corey

And you cut a function call

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 07:25:43

Using {-32} takes away two arithmetic operations.

Select dateadd(day, 10, dateadd(month, (2006 - 1900) * 12 + 12, -32))

Day = 10
Year = 2006
Month = 12

Just to display which 12 (or number) belongs to which information part.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-12-31 : 08:00:18

But magic # 32 will not always work...

Select dateadd(day, 10, dateadd(month, (2006 - 1900) * 12 + 10, -32)) -- 20061010
Select dateadd(day, 10, dateadd(month, (2006 - 1900) * 12 + 11, -32)) -- 20061109 !!!!
Select dateadd(day, 10, dateadd(month, (2006 - 1900) * 12 + 12, -32)) -- 20061210

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 08:16:56

Good point!

Too much egg-nodd for me, I presume?

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-12-31 : 08:26:55

Happy New Year Peso!
Go easy on the egg-nodd

rockmoose

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-31 : 08:47:01

ok i tried
select * from cancelled where (cancelled >=cast('20061231' as datetime) and cancelled<=cast('20061231' as datetime))
there is a record there cancelled with todays date but it is returning no records.
am i doing somethign wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 08:48:58

Using only todays DATE, assumes the time to be 00:00:00.

select * from cancelled
where cancelled >= '20061231' and cancelled < '20070101'

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-31 : 08:57:25

i'm passing in the date -- is there a way within the cast to set teh second to be cast(date+1)?
or would I Need the dateadd for that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 12:03:26

select * from cancelled
where cancelled >= @YourDate and cancelled < DATEADD(day, 1, @YourDate)

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-31 : 13:12:49

thanks the cast is what i need.
just tell me if I pass in a date 200711 will it mess up? do I need to add the 0's?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-31 : 19:01:48

you will need to add the 0s...

to Rockmoose:
Yeah.. I've been pretty swamped... and i just got another baby girl for christmas (22nd)

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources