June 5, 2009 at 3:36 pm
bdmtsu (6/5/2009)
I was doing something a little different. I had a column of dates that needed to be put in the MM/DD/YYYY format so it could be imported into SAS.Most of the examples used getdate(), but Farrell Keough's example worked perfectly for me!
I used SELECT CONVERT(varchar, Dte_Eligibility_Begins , 101) and it converted 2008-01-01 00:00:00 to 01/01/2008.
Some of the other examples still put the time part in, even on just using getdate().
Thanks for the help.
Datetime values don't have format.
Open the same table with datetime values in EM and QA. See the difference? But those datetimes are IDENTICAL!
If you start talking about formats it's not about datetime anymore, it's about string representation of date/time values.
And string representation depend on locale settings, preferred formatting, etc.
And it's not about date/time only. I can say the same thing about numbers.
Are these numbers different: 5 or 101? How can you tell?
They look different, but may be it's only representation what is different, but numbers are actually the same?
You know why programmers (old school, those who studied computing, not just .net) mix Helloween with Christmas?
Because 31OCT = 25DEC.
_____________
Code for TallyGenerator
June 6, 2009 at 1:12 am
Sergiy (6/5/2009)
You know why programmers (old school, those who studied computing, not just .net) mix Helloween with Christmas?Because 31OCT = 25DEC.
LOL :hehe:
Haven't seen that one for a long long time!
Do you know if there's any class during .net studying where the meaning of it including underlying logic is deeply explained so our .net fellows (aka drag'n'drop parameterizer) can participate? 😉
To all .NET fans that may come across: It's a joke, no offence, so don't take it too serious!
June 9, 2009 at 4:10 pm
This may be relevant to you. You should consider the following in this type of comparison...
While the syntax of convert(varchar,getdate(),101) will return the data in the format you are expecting, the date will infact return as varchar.
Appearances say this is not a problem... And it's not (excluding best practices, performance and integrity) as long as one of the datatypes being compared is of datetime and both will be implicity converted. But run this statement that compares the date values as varchar and as datetime then see where your comparisons may have an issue.
--
IF '11/01/2008' > '01/01/2009' -- Nov of 2008 is not greater than any date in 2009
PRINT 'SEE THE ISSUE?'
GO
IF cast('11/01/2008' as datetime) > '01/01/2009'
PRINT 'SEE THE ISSUE? Not here!'
ELSE
PRINT 'SEE IT CLEARLY YET?'
--
Since from the previous post in this thread it looks like you will eventually be comparing these two values with > or < operators, this will be very important to your comparisons.
Remember values with the datetime data type are stored internally by SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.
So while you see '06/01/2008' SQL Server sees 39598/0
The varchar values of 2008-06-01 and 06/01/2008 are not equal, but the datetime values of 2008-06-01 and 06/01/2008 ARE equal.
-
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply