1

I have a php script inserting values in to SQL Server 2005 table via SQL

$date = '15 Jun 2011 00:00';
$sql = "INSERT INTO shows ( DateStart ) VALUES ( '$date')";

I have 2 issues:

1) how best to convert this date into something SQL Server understands? The default format is mm/dd/yyyy on my system but I want to present the dates as readable to the user.

2) When inserting an empty string the DateStart value gets set to 1/1/1900. How best to convert an "invalid" date into a NULL (the column already accepts NULLs) so that if $date = "" then DateStart IS NULL

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
danielc
  • 55
  • 2
  • 5

2 Answers2

5
  • Point 1: Use ISO date format YYYY-MM-DDTHH:MM:SS
    Note: For date only use YYYYMMDD becuase of anomalies in how SQL Server parse dates
  • Point 2: NULLIF(value, '')
gbn
  • 422,506
  • 82
  • 585
  • 676
  • @Mark S. Rasmussen: in the comment bunfight here to both answers (was me and @marc_s) with further links http://stackoverflow.com/questions/1138142/best-way-to-convert-and-validate-a-date-string – gbn Jun 01 '11 at 09:01
0

  1. I think the datetime format while inserting the record in datetime column should always "YYYY-MM-DD HH:MM:SS" Default Format

  2. gbn already told.

Pankaj
  • 9,749
  • 32
  • 139
  • 283
  • The linked article doesn't seem to contain any recommendations on using the `YYYY-MM-DD HH:MM:SS` format. – Andriy M Jun 01 '11 at 09:34
  • Please take a look at [this answer](http://stackoverflow.com/questions/1138142/best-way-to-convert-and-validate-a-date-string/1138203#1138203), as it shows clearly when this format is not reliable. – Andriy M Jun 01 '11 at 09:37
  • Thank You Andriy. That's the reason for 81.9K reputation of gbn. I was inserting the record in database for datetime column and it stored it without T – Pankaj Jun 01 '11 at 11:32
  • It displays `datetime` values without `T`, yes. That's very misleading. It's great there are sites like SO where you can learn such things. – Andriy M Jun 01 '11 at 11:50