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.
| Author |
Topic |
|
muzammal_janjua
Starting Member
1 Post |
 Posted - 2010-01-21 : 23:39:07
|
Hello everyoneits my first post in this group, so i hope a good cooperative response from you guys.I am having a problem in my MS sql server Database, to support my question i have attached a image , which itself explains the issue. the dark highlighted area shows a date like 3/8/2009 and light color highlighted area show dates like 2/13/2009 etcformat looks like mm/dd/yyyyBut when i get this date in my VB program, the format changes it like it makes 3/8/2009 as 3 August 2009and it makes 2/13/2009 as 13 Feb 2009means the dates in which middle value is less then 10, it convert that into month, otherwise into day.Why??i m using this in CODE to convert into my specific formatGlobal Const gSFormat As String = "DD/MM/YYYY"mtxtdate = format(sqlDate,gSFormat)Kindly help..Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-22 : 01:54:37
|
| The image you have linked to is on your C: drive, so I'm afraid we can't see it. You'll have to upload it somewhere on the internet that you can point to.SQL stored DATETIME in an internal format that preserves it and allows it to be treated chronologically - e.g. using date functions to add days / months etc., or compare dates.If you retrieve a DATETIME column from SQL into a Datetime variable in VB I would expect VB to do the same.You should then apply formatting at the final point, when you display it to the user.If you are retrieving dates from SQL in string/varchar format then you will be at the mercy of the date conversions involved, which may use the Locale setting on the machine (i.e USA, Europe and Japan settings will cause different results), and your VB may make a different conversion to your SQL conversion.If you want to present a date to SQL in string format always use yyyymmdd format as SQL will treat this unambiguously (note that to be treated as unambiguous there must be NO hyphens in this format) |
 |
|
|
Aiby
Yak Posting Veteran
71 Posts |
Posted - 2010-01-24 : 10:16:05
|
I think this should ask in any VB forum! Not an MS SQL related issue. Any way…One easy way to resolve this issue from VB while dealing with MS SQL server or any database with date type field, input the data (date type) to the database in a format "dd/mmm/yy" format! Eg:- '8-Aug-09' or '08/Aug/09' Only thing you must ensure it is a valid date! quote:
Originally posted by muzammal_janjua
Hello everyoneits my first post in this group, so i hope a good cooperative response from you guys.I am having a problem in my MS sql server Database, to support my question i have attached a image , which itself explains the issue.the dark highlighted area shows a date like 3/8/2009 and light color highlighted area show dates like 2/13/2009 etcformat looks like mm/dd/yyyyBut when i get this date in my VB program, the format changes it like it makes 3/8/2009 as 3 August 2009and it makes 2/13/2009 as 13 Feb 2009means the dates in which middle value is less then 10, it convert that into month, otherwise into day.Why??i m using this in CODE to convert into my specific formatGlobal Const gSFormat As String = "DD/MM/YYYY"mtxtdate = format(sqlDate,gSFormat)Kindly help..Thanks
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-24 : 10:22:04
|
quote:
Originally posted by Aiby
I think this should ask in any VB forum! Not an MS SQL related issue. Any way…One easy way to resolve this issue from VB while dealing with MS SQL server or any database with date type field, input the data (date type) to the database in a format "dd/mmm/yy" format! Eg:- '8-Aug-09' or '08/Aug/09' Only thing you must ensure it is a valid date! quote:
Originally posted by muzammal_janjua
Hello everyoneits my first post in this group, so i hope a good cooperative response from you guys.I am having a problem in my MS sql server Database, to support my question i have attached a image , which itself explains the issue.the dark highlighted area shows a date like 3/8/2009 and light color highlighted area show dates like 2/13/2009 etcformat looks like mm/dd/yyyyBut when i get this date in my VB program, the format changes it like it makes 3/8/2009 as 3 August 2009and it makes 2/13/2009 as 13 Feb 2009means the dates in which middle value is less then 10, it convert that into month, otherwise into day.Why??i m using this in CODE to convert into my specific formatGlobal Const gSFormat As String = "DD/MM/YYYY"mtxtdate = format(sqlDate,gSFormat)Kindly help..Thanks
try to always pass dates in iso format (yyyy-mm-dd) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-24 : 13:08:56
|
| Beware that 'yyyy-mm-dd' will not implicitly cast in all locales. Needs to have no hyphens - i.e. yyyymmdd - to be certain to cast implicitly. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-25 : 03:16:24
|
| <<try to always pass dates in iso format (yyyy-mm-dd)>>You can use that format as long as you use time portion tooyyyy-mm-ddTHH:MM:SSMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Date Issue