Date in SQL insert

I need to put a date into and SQL insert statement. What charater is it
that the date needs to be wrapped in?
Thanks
Lee
hi Lee,
"Lee Trotter" <latrotter@.@.@.sympatico.ca> ha scritto nel messaggio
news:uantLKdbEHA.3204@.TK2MSFTNGP09.phx.gbl...
> I need to put a date into and SQL insert statement. What charater is it
> that the date needs to be wrapped in?
if you use ISO date format, you will be sure the data you provide will be
interpreted correctly without problems...
enclose the value in single '
SET NOCOUNT ON
DECLARE @.t TABLE (
d DATETIME
)
INSERT INTO @.t VALUES ('20040720') -- as 20th july, 2004 =;-D
INSERT INTO @.t VALUES ('20040720 18:50:00') -- with time frame
SELECT * FROM @.t
--<--
d
2004-07-20 00:00:00.000
2004-07-20 18:50:00.000
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Ok the issue I now have is that sql server is accepting the insert with the
date but the month and year are reversed. I am executing this though a
VB.Net call command object Here is the insert statement
INSERT INTO tblCards (fldProjectID, fldDateTime, fldDescription) VALUES
('1240', '19/07/2004 8:09:37 PM', 'description test here 4')
But I get the error "The conversion of a char data type to a datetime data
type resulted in an out-of-range datetime value" If I reverse the month &
day it works
"INSERT INTO tblCards (fldProjectID, fldDateTime, fldDescription) VALUES
('1240', '07/19/2004 8:09:37 PM', 'description test here 4')"
Any ideas?
Thanks
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2m3098FiddpiU1@.uni-berlin.de...
> hi Lee,
> "Lee Trotter" <latrotter@.@.@.sympatico.ca> ha scritto nel messaggio
> news:uantLKdbEHA.3204@.TK2MSFTNGP09.phx.gbl...
> if you use ISO date format, you will be sure the data you provide will be
> interpreted correctly without problems...
> enclose the value in single '
> SET NOCOUNT ON
> DECLARE @.t TABLE (
> d DATETIME
> )
> INSERT INTO @.t VALUES ('20040720') -- as 20th july, 2004 =;-D
> INSERT INTO @.t VALUES ('20040720 18:50:00') -- with time frame
> SELECT * FROM @.t
> --<--
> d
> 2004-07-20 00:00:00.000
> 2004-07-20 18:50:00.000
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||> Ok the issue I now have is that sql server is accepting the insert with
the
> date but the month and year are reversed. I am executing this though a
> VB.Net call command object Here is the insert statement
> INSERT INTO tblCards (fldProjectID, fldDateTime, fldDescription) VALUES
> ('1240', '19/07/2004 8:09:37 PM', 'description test here 4')
Yes, stop using this ambiguous regional format! Use YYYYMMDD and you will
never have this problem.
http://www.aspfaq.com/
(Reverse address to reply.)
|||hi Lee,
"Lee Trotter" <latrotter@.@.@.sympatico.ca> ha scritto nel messaggio
news:OM65l9ebEHA.2292@.TK2MSFTNGP09.phx.gbl...
> Ok the issue I now have is that sql server is accepting the insert with
the
> date but the month and year are reversed. I am executing this though a
> VB.Net call command object Here is the insert statement
> INSERT INTO tblCards (fldProjectID, fldDateTime, fldDescription) VALUES
> ('1240', '19/07/2004 8:09:37 PM', 'description test here 4')
> But I get the error "The conversion of a char data type to a datetime data
> type resulted in an out-of-range datetime value" If I reverse the month &
> day it works
> "INSERT INTO tblCards (fldProjectID, fldDateTime, fldDescription) VALUES
> ('1240', '07/19/2004 8:09:37 PM', 'description test here 4')"
>
you can use the SET DATEFORMAT xxx hint, like
SET NOCOUNT ON
DECLARE @.t TABLE (
ID INT IDENTITY ,
d DATETIME
)
SET DATEFORMAT dmy
INSERT INTO @.t VALUES ('20/07/2004 16:20')
SET DATEFORMAT ymd
INSERT INTO @.t VALUES ('2004/07/20 16:21')
SET DATEFORMAT mdy
INSERT INTO @.t VALUES ('07/20/2004 16:20')
SELECT * FROM @.t
--<--
ID d
-- ---
1 2004-07-20 16:20:00.000
2 2004-07-20 16:21:00.000
3 2004-07-20 16:20:00.000
for the complete synopsis please have a look at
http://msdn.microsoft.com/library/de...t-set_052s.asp ,
but please keep in mind the format SQL Server better accepts, without
regional settings troubles, is the ISO YYYYMMDD format
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Also Lee, bear in mind that if you do not follow Andrea's advice to use ISO
formatted dates, your code is not portable between machines of different
regional settings. Hard coding formats is never (almost never) a very good
idea.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2m4o76Fi8c2bU1@.uni-berlin.de...[vbcol=seagreen]
> hi Lee,
> "Lee Trotter" <latrotter@.@.@.sympatico.ca> ha scritto nel messaggio
> news:OM65l9ebEHA.2292@.TK2MSFTNGP09.phx.gbl...
> the
data[vbcol=seagreen]
&
> you can use the SET DATEFORMAT xxx hint, like
> SET NOCOUNT ON
> DECLARE @.t TABLE (
> ID INT IDENTITY ,
> d DATETIME
> )
> SET DATEFORMAT dmy
> INSERT INTO @.t VALUES ('20/07/2004 16:20')
> SET DATEFORMAT ymd
> INSERT INTO @.t VALUES ('2004/07/20 16:21')
> SET DATEFORMAT mdy
> INSERT INTO @.t VALUES ('07/20/2004 16:20')
> SELECT * FROM @.t
> --<--
> ID d
> -- ---
> 1 2004-07-20 16:20:00.000
> 2 2004-07-20 16:21:00.000
> 3 2004-07-20 16:20:00.000
> for the complete synopsis please have a look at
>
http://msdn.microsoft.com/library/de...t-set_052s.asp ,
> but please keep in mind the format SQL Server better accepts, without
> regional settings troubles, is the ISO YYYYMMDD format
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>