Converting DateTime to SqlDateTime format

Hi,

I have a function that generates a range of DateTimes, which I then cast to SqlDateTime to compare with SqlDateTime values in a database.

The problem is my converted DateTimes come out in this type of format "6/2/2006 12:00:00 AM"

wheras my SqlDateTimes in the database are in this format "2006-01-18T00:00:00.0000000-12:00"

Any ideas how I can convert the DateTime values to SqlDateTime correctly so that I can compare them? As I said I tried creating a new SqlDateTime object with the DateTime value ie

DateTime dt = new DateTime("");

SqlDateTime sdt = new SqlDateTime(dt);

But that doesn't work correctly, its still not in the format that is in the database.

Assuming you are using the datetime datatype, the format that it is in the database is not"2006-01-18T00:00:00.0000000-12:00". From Books Online, the database actually stores datetime values as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

So, the"2006-01-18T00:00:00.0000000-12:00" is just an output representation of that value.

If you give us a little more information about what you are trying to do we should be better able to help you.

Convert DateTime to NVARCHAR

Hi,
My test table looks like this:
1 Id int 4 0
0 DateTimes datetime 8 1
0 DatoTid nvarchar 14 1
With the following content:
1 15-10-2005 10:22:00 15102006102200
2 16-10-2005 11:16:00 17102005111600
I would like to show the records that are different, but I can't see
how to get rid of the <->, <:> and <space> characters.
It would be enough only to get the records that has differents dates
e=2Eg. 16-10-2005<>17102005. I have tried the following statement in
diffrents ways but...
SELECT [Id], CONVERT(NVARCHAR(10), DateTimes, 113) as DatTid, DatoTid
FROM [SQL_Eksempler].[dbo].[Dato]
WHERE CONVERT(NVARCHAR(10), DateTimes, 113)<>Datotid
/Ren=E9Hi
To do a string comparison try using convert for your datatime and then use
replace to get rid of the unwanted characters:
SELECT id, CAST(REPLACE(CONVERT(CHAR(10), DateTimes, 105),'-',SPACE(0)) +
REPLACE(CONVERT(CHAR(8), DateTimes, 108),':',SPACE(0)) AS NVARCHAR(14)),
DatoTie
FROM #Dates
WHERE CAST(REPLACE(CONVERT(CHAR(10), DateTimes, 105),'-',SPACE(0)) +
REPLACE(CONVERT(CHAR(8), DateTimes, 108),':',SPACE(0)) AS NVARCHAR(14)) <>
DatoTie
John
"refdk" wrote:

> Hi,
> My test table looks like this:
> 1 Id int 4 0
> 0 DateTimes datetime 8 1
> 0 DatoTid nvarchar 14 1
> With the following content:
> 1 15-10-2005 10:22:00 15102006102200
> 2 16-10-2005 11:16:00 17102005111600
> I would like to show the records that are different, but I can't see
> how to get rid of the <->, <:> and <space> characters.
> It would be enough only to get the records that has differents dates
> e.g. 16-10-2005<>17102005. I have tried the following statement in
> diffrents ways but...
>
> SELECT [Id], CONVERT(NVARCHAR(10), DateTimes, 113) as DatTid, DatoTid
> FROM [SQL_Eksempler].[dbo].[Dato]
> WHERE CONVERT(NVARCHAR(10), DateTimes, 113)<>Datotid
> /René
>|||Thanks a lot for your help this was exactly what I needed.
Ren=E9