Datetime format regardless regional settings

Hi,

in tsql there is a common format for datetime (regardless regional settings)?

I use : 'mm/dd/yyyy' to access cols in database,

but someone says that the right one is: yyyy-mm-dd.

Any suggestion about that?

Thank a lot

I believe you want the ISO format -- 112; that is yymmdd. Can somebody double check this?

Dave

|||

yes dave.. 112 is the ansi unseperated date format.....

so best option is to get all ur date in this format: select convert(varchar, getdate(), 112)

|||

Hi,

I tried thid on nortwind DB (date are: dd/mm/yyyy)

select * from orders where orderdate < '19960704'

this does work, no data are retrieved,

but works for:

select * from orders where orderdate < '07/04/1996'

select * from orders where orderdate < '1996-07-04'

both select one row (if nortwind is the original one)

?

any suggestion?

All extract the same regardless the regional settings?

|||

Fast:

I tried all three queries against my copy of the northwind database. The lowest date that I have in the orders table is the date '07/04/1996'. For this reason, I do not get any rows returned with any of the select statements. However, when I change the "less than" operator to a "less than or equal to" operator I get the expected row. I am not getting the same results as you. I am afraid I can't help on this one. Sorry fo adding to confusion.

Dave

|||

hmm..works fine with me...the lowest date in northwind is..1996-07-04 00:00:00.000..

i get 0 rows for all the queries u mentiones...and 1 each when i use <= ..

neways...wat do u want to achieve exactally....see 112 is the ansi date format, which shud be used for date conversion and comparisons as its the standard....select convert(varchar(10), getdate(), 112) ...

yyyy-mm-dd +time is how sql server present it as..

|||

Sorry for having written too fast,

all three select retrieve 1 record if date is minor than 5 july 1996,

I have tried all queries with several regional settings and everything work.

If anyone has other suggestion, is welcome

Besides in internet there are this interestings articles:

http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-delimit/format-dates-for-database-entry.html

and

http://www.karaszi.com/SQLServer/info_datetime.asp

Thank

DateAdd expression works in tsql but doesn't work in ssis

Hi There,

I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?

dateadd("dd", -1, datediff("dd", 0, getdate()))

Thanks.

Some more info please. What do you mean by "it doesn't work"? Do you get an error or the wrong result?

If the latter, tell us what you result you get and also what result you are expecting to get.

Thanks

-Jamie

|||DateDiff returns an integer while DateAdd expects a datetime in that position. T-SQL is able to implicitly cast dates to integers, while SSIS cannot.

|||

Ok..If you run the below query in query analyzer..

select dateadd("dd", -1, datediff("dd", 0, getdate()))

it gives me.."2007-05-08 00:00:00.000". I would like to get the same value in ssis. In ssis, if I use the above as an expression for a variable, I get a design time error. "The expression for variable failed evaluation, there was an error in the expression".

Thanks for responding.

|||

Ok..you are right..so can i cast it like this..

dateadd("dd", -1, (DT_DBTIMESTAMP)(datediff("dd", 0, getdate()))). This doesn't work either. How do I cast it?

Thanks.

|||

Sam_res03 wrote:

Ok..you are right..so can i cast it like this..

dateadd("dd", -1, (DT_DBTIMESTAMP)(datediff("dd", 0, getdate()))). This doesn't work either. How do I cast it?

Thanks.

You'd have to use DateAdd to perform the cast from integer to date and thus define 0 as 1/1/1900 the way T-SQL does.

dateadd("dd", -1,
dateadd("dd",
datediff("dd",
dateadd("dd",0,(DT_DBDATE)"1/1/1900")
, getdate())
,(DT_DBDATE)"1/1/1900")
)

|||

Hi Jay,

Thanks for your reply. I really appreciate it. Event though your sol works, I thought I would use this instead..

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 00:00:00") as this was much readable. I am sure this works for all situations.

So

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 00:00:00")

gives 5/8/2007 00:00:00

and

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 23:59:59")

gives 5/8/2007 11:59 PM

I am not sure which one is efficient though, probably yours...

Thanks